Welcome to the Learning FOCUS blog series. If this is your first post, I recommend you start with Introducing an open billing data format to get a high-level picture of what the FinOps Open Cost and Usage Specification (FOCUS) is and what it covers. This week, I’ll cover something simple, yet crucial for measuring cost over time: the date columns. You’ll use these to filter and group data for targeted or over-time trend analysis.

⮜ Previous post (Charges + pricing)   ·   Next post (Resources) ⮞

How dates work in FOCUS

Before we get into the date columns, let’s cover how dates work in FOCUS. Dates in FOCUS are formatted using the ISO 8601 date/time format, which includes the date and time, including minutes and seconds. All dates are expected to be UTC for consistency, regardless of what time zone the provider is in.

yyyy-MM-ddTHH:mm:ssZ

2025-01-01T00:00:00Z

This is instead of the US standard “MM/dd/yyyy” format used in actual and amortized datasets.

Most dates in FOCUS are also periods with start and end dates. And what’s unique about this is the end dates are exclusive end dates. Instead of showing the end of the month as Jan 31 (or 2025-01-31T12:59:59Z), FOCUS uses the exclusive end date of Feb 1 (or 2025-02-01T00:00:00Z). Exclusive end dates simplify date filtering with a less than filter that works regardless of the level of precision used in dates (e.g., seconds vs. milliseconds) or even a change as simple as how many days are in a month (i.e., 28 vs. 29 vs. 30 vs. 31) as you update queries from one month to the next. (And besides, if we’re being overly pedantic, charges for a day cannot be finalized until midnight the next day anyway.)

The start and end period approach also supports any granularity the provider may choose. For example:

Billing and charge periods

The FOCUS schema defines two sets of dates: billing period and charge periods. Of course, these aren’t unique to FOCUS, so they should be familiar to anyone.

The billing period is the date range you’ll find on your invoice. FOCUS defines BillingPeriodStart and BillingPeriodEnd for this date range. EA and MCA accounts both align to calendar months, so you’ll generally see dates aligning to the first of the month.

The charge period is the date range of the usage or purchase. For usage, the charge period is usually the same granularity as the dataset. Monthly grain data would have a month period, while daily would have a day, and hourly would have an hour. For purchases of time-constrained services, the charge period is the period that the purchase is valid. As an example, a one-year commitment discount purchase should have a charge period of the one year that commitment discount is active. For purchases of perpetual services that do not end or expire, the charge period behaves like usage and is usually the same granularity as the dataset. FOCUS defines ChargePeriodStart and ChargePeriodEnd for this date range. Microsoft Cost Management only supports daily grain data today but is planning to make hourly data available via Cost Management exports in the future.

Microsoft-specific dates

Looking beyond columns defined as part of FOCUS, Microsoft also adds a service period and single date that the exchange rate was locked. Since these are external, custom columns, you generally won’t find these columns in other provider datasets.

The service period is generally only applicable for MCA purchases and behaves the same way charge periods do. Cost Mangaement defines x_ServicePeriodStart and x_ServicePeriodEnd columns for this date range primarily for backwards compatibility. These columns may be removed in a future release, given the overlap with charge periods.

The exchange rate date is the date the pricing currency to billing currency exchange rate was locked. This date does not necessarily mean the exchange rate was updated. Microsoft currently updates exchange rates twice a year to minimize constant price fluctuations, but exchange rate dates are typically aligned to the billing period start date.

Transitioning to FOCUS

Whether you’re updating reports, transforming data, validating FOCUS, or simply curious about how FOCUS compares to the historical actual and amortized datasets, you’re probably looking for a more direct mapping of columns. We have separate articles covering each of these scenarios in more detail, but here’s a summary regarding the date columns I covered above.

¹ FOCUS uses exclusive end dates, so you need to add 1 day to the daily granularity Cost Management date or 1 hour to hourly data, when available.

For more details, refer to the following articles:

• Update reports to use FOCUS columns
• Convert cost and usage data to FOCUS 
• Validate FOCUS data 

Reviewing cost in Power BI

Now that we’ve covered date columns, we can start to look at how costs change over time. Nearly every page in FinOps toolkit reports looks at cost over time. I’ve covered several of these already, but will focus on a few examples of how date columns are used in the FinOps toolkit Cost summary report.

Before I show this, let me call out that this is a sneak peak of a design refresh that’s coming in FinOps toolkit 0.8. So if this doesn’t look like what you see today, that’s why.

First up is showing the accumulated cost or running total for a period. The Summary page provides the running total for effective cost, negotiated discount savings, and commitment discount savings. I like this example because it allows you to see the cost trend towards a budget within a specific period using the date filter. Whether you need to track a monthly, quarterly, or yearly budget, you can tweak the dates to see how you’re trending.

Next up is the Services page, which shows the daily or monthly cost broken down by service. There are many other pages that offer a similar breakdown by other attributes, like subscriptions, regions, and resources, but they use the same basic concept. (Note that the KQL-based reports allow selecting either daily or monthly granularity by default to facilitate the right level of detail for you, which is what we’re seeing here.) Monthly and especially daily data makes it easier to identify cost variance over time. As an example, in the screenshot below, we can easily see Compute costs growing 30% in March, 50% in June, and 80% in July.

And lastly, I’ll show the Purchases page which is similar to the previous example with the exception that daily data for purchases behaves differently than daily data for usage since usage comes in at a consistent daily granularity. Purchases are generally monthly, which you can see here, but they could also be one-time or even yearly, depending on the service. Differentiating based on ChargeCategory can help when clearly identifying the types of charges that naturally behave differently. This is crucial when defining a forecast model.

Hopefully, these examples spur ideas for how to leverage dates in your own reports. We’ll continue dig into these pages in more detail in future posts. To learn more about these and other reports, see FinOps toolkit Power BI reports.

Querying cost in FinOps hubs

Now let’s look at a few queries you can run for those using FinOps hubs with Data Explorer. Unlocking dates opens a whole new world of opportunities, but we’ll keep it simple with a few different date-based patterns that you can explore on your own.

Let’s start simple with cost summarized by day. To keep it a little interesting, we can group by PricingCategory to view committed vs. on-demand (standard) spend.

Costs | where ChargePeriodStart >= ago(30d) | summarize EffectiveCost = round(sum(EffectiveCost), 4) by ChargePeriodStart = startofday(ChargePeriodStart), PricingCategory, BillingCurrency | render linechart

We can switch this to monthly is by changing “startofday” to “startofmonth”, but let’s try something a little more complex before we look at monthly costs. Similar to the Summary page in Power BI I shared above, let’s say you want to see a trend towards a monthly budget. We’ll use the KQL row_cumsum() function to accumulate a running total by month. To do this, we need to

// Define the month names we want to use let monthname = dynamic(['', 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']); // Costs // // Only look at this month and last month | where ChargePeriodStart >= startofmonth(now(), -1) // // Sum costs and track the | summarize EffectiveCost = sum(EffectiveCost), ContractedCost = sum(ContractedCost), ListCost = sum(ListCost) by ChargePeriodStart | extend CommitmentDiscountSavings = ContractedCost – EffectiveCost | extend NegotiatedDiscountSavings = ListCost – ContractedCost // // Sort rows and calculate the accumulated cost and savings for each month | order by ChargePeriodStart asc | extend Month = strcat(format_datetime(ChargePeriodStart, 'yyyy-MM '), monthname[monthofyear(ChargePeriodStart)]) | extend EffectiveCostRunningTotal = row_cumsum(EffectiveCost, prev(Month) != Month) | extend CommitmentDiscountSavingsRunningTotal = row_cumsum(CommitmentDiscountSavings, prev(Month) != Month) | extend NegotiatedDiscountSavingsRunningTotal = row_cumsum(NegotiatedDiscountSavings, prev(Month) != Month) // // Render output | project ChargePeriodStart, CommitmentDiscountSavingsRunningTotal, NegotiatedDiscountSavingsRunningTotal, EffectiveCostRunningTotal, Month | render areachart

This gives us an interesting view showing the accumulated cost for each calendar month, which is slightly different than what you saw in Power BI earlier. And if you’re interested in seeing more months, simply change the -1 to a higher number or even remove the filter altogether to see all data.

This query is part of a new Data Explorer dashboard that will come in the FinOps toolkit 0.8 release later this month. The dashboard includes hundreds of queries to help you visualize your costs across columns we’ve shared and many we will cover in the future.

Going back to monthly cost, let’s look at our billed and effective cost using BillingPeriodStart to base our query on when costs are billed rather than when costs are incurred.

Costs | summarize BilledCost = round(sum(BilledCost), 2), EffectiveCost = round(sum(EffectiveCost), 2) by BillingPeriodStart, BillingCurrency | render columnchart

And we can take this one step further to look at the change percentage:

Costs | summarize BilledCost = sum(BilledCost), EffectiveCost = sum(EffectiveCost) by BillingPeriodStart = startofmonth(BillingPeriodStart) | order by BillingPeriodStart asc | extend PreviousBilledCost = prev(BilledCost) | extend PreviousEffectiveCost = prev(EffectiveCost) | project BillingPeriodStart, BilledCost = iif(isempty(PreviousBilledCost), todouble(''), delta(PreviousBilledCost, BilledCost) * 100.0), EffectiveCost = iif(isempty(PreviousEffectiveCost), todouble(''), delta(PreviousEffectiveCost, EffectiveCost) * 100.0)

Again, both of these examples are included in the new Data Explorer dashboard coming out later this month. Stay tuned for updates there.

We’ll continue to build on these columns in future blog posts to demonstrate more interesting scenarios as we go. Leave a comment if you’d like to see any specific queries and I’ll make sure to cover it.

What next?

At this point, we have a high-level understanding of the types of charges we’re incurring, how much we’re being charged, and when we incurred those charges. Next, we’ll dig into the columns that identify and describe the cloud resources we’re deploying that incur those charges. There’s a lot to cover, so get ready.

If you need a refresher or have any questions about previous topics, this is a good time to review them. We’ll touch on a little of everything given the overlapping concepts.

• Introduction
• Cost columns
• Charge types and pricing models

For a more directed walkthrough, the FinOps Foundation offers a free Introduction to FOCUS course. When you’re ready to dig into your own FOCUS data, check out the Power BI reports in the FinOps toolkit. These reports offer a great starting point that you can customize to meet your needs. And if you’re looking for more advanced analytics that can handle data at scale, check out FinOps hubs, which offer additional benefits, like pre-calculated savings for EA and MCA accounts.

⮜ Previous post (Charges + pricing)   ·   Next post (Resources) ⮞