Custom Time Intelligence formulas in DAX: Financial Year June to July

Many companies operate across financial years rather than calendar years, and still others operate across a 4-4-5 calendar or some variation thereof.

This post will talk about reporting numbers across financial years – that is,  starting on the 1st of July and finishing on the 30th of June.

You may be familiar with some of DAX’s inbuilt time intelligence formulas, such as TOTALMTD, DATESYTD and so on, but alas, there aren’t similar ones available for FYTD (Financial Year to Date), or 4-5-5 calendars. Perhaps one day they will become available, but until then we have to rely on custom time intelligence formulas, or some manipulation of the inbuilt formulas.

The other significant drawback to inbuilt formulas is that your data has to have daily granularity. This may simply not be available, in which case you have to group data to coincide with the last day of the week or month, or quarter, etc. Or, if you have millions of rows of data and you don’t need to evaluate things at daily granularity, you may just have to suffer the resulting performance hit.

The good thing about using a custom time intelligence formula is that the structure of the formula is quite powerful and adaptable to a variety of different scenarios (whether you want to report on financial years, quarters, financial year quarters, weeks, arbitrarily defined seasons, or a combination of the aforementioned). Your data can also be at any granularity, and you get to develop a much deeper understanding of how the result is obtained, making it possible to tailor formulas to give you exactly what you’re after.

The basic structure of a custom time intelligence formula has been covered extensively by others, and I also like Matt’s explanation here. They can seem a bit daunting to write at first, but once you’ve done it a few times, the penny drops and it becomes quite easy. I strongly recommend you read their posts for a full understanding of how the formula works, but that’s not the aim of this post.

I’ll do a few examples at different levels of granularity to illustrate.

Assuming you have some data at daily granularity, you might have defined a measure aggregating your total sales:

Total Sales =
SUM ( Salestable[SalesQty] )

You would then have to create a Financial Year (text format) column and a Financial Year Index column (numeric format) in your Calendar, assuming it’s actually called “Calendar” (you can follow instructions on how to do that here), and once you have those, you can define the FYTD sales formula as follows:

Sales FYTD =
CALCULATE (
[Total Sales],
    FILTER (
        ALL ( ‘Calendar’ ),
‘Calendar'[FinYearIndex] = MAX ( ‘Calendar'[FinYearIndex] )
&& ‘Calendar'[Date] <= MAX ( ‘Calendar'[Date] )
    )
)

Looking at the formula, you might realise why it is necessary to have a numeric index column for FinYear. Hint: It’s because of the MAX function, which only accepts numeric data. DAX doesn’t know that the text “2017/18” represents a greater value than “2016/17”, which is why the numeric Index value is needed.

But there is also an alternative option you could use if your data is at daily granularity, which doesn’t require the custom time intelligence formula or an index column. In fact, it relies on one of the built-in time intelligence aggregation functions, where you can specify the year end date as a text string:

Sales FYTD =
CALCULATE (
[Total Sales],
    DATESYTD ( ‘Calendar'[DATE], “June 30” ),
    ALL ( ‘Calendar’ )

)

If you left out the optional “June 30” part of the formula, it would revert to the default, which is December 31. Of course you could type any end date here if you wish, and the formula should still work.

Now let’s consider data at monthly granularity. Say you have sales data aggregated by Yearmonth (format YYYYMM, but make sure it’s formatted as a number) . The structure of the formula is exactly the same:

Sales FYTD =
CALCULATE (
[Total Sales],
    FILTER (
        ALL ( ‘Calendar’ ),
‘Calendar'[FinYearIndex] = MAX ( ‘Calendar'[FinYearIndex] )
&& ‘Calendar'[Yearmonth] <= MAX ( ‘Calendar'[Yearmonth] )
    )
)

If your aggregation time interval is specified as text, you may need to create a numeric index column for it first.

So there you have it, a few formulas for aggregating data across financial years, allowing for different levels of granularity.

Please share if you found the post useful, and feel free to leave a comment.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s