So, I had a fantastic time at TechEd Africa last week. I had the privilege of presenting two sessions, one of them being a session on DAX, the expression language for PowerPivot. In that session, I made the following statement:
“DAX time calculation functions; such as YTD, QTD, MTD; only understand Calendar time, with no support for custom Financial Time hierarchies”
In this blog post, I’d like to correct myself. Because, I wasn’t entirely correct 🙂
In fact, DAX does provide some support for custom Financial Time Hierarchies; as it allows for the last day of the financial year to be specified in a number of the time calculation functions.
e.g. let’s look at the typical Year to Date calculation for Sales Amount in Contoso.
The DAX version of this would look something like:
However, TOTALYTD also provides support for two optional parameters, namely Filter and Year_End_Date.
So, if we wanted to calculate the YTD for a financial year which is April – March, the DAX would look something like:
=TOTALYTD(SUM(FactSales[SalesAmount]), DimDate[DateKey], ALL(FactSales), “31 Mar”)
(so, the Filter basically does nothing, and the Year_End_Date is simply the date and month – yes I specify the month name as text to avoid regional setting issues! Also, including the year in the parameter is not required and won’t really make sense as we would want the expression to evaluate for any year)
The result of these statements looks as follows:
You’ll notice that the Financial YTD resets itself in April as required.
So, yes, there is support for Financial time.
However, I wasn’t entirely incorrect in my statement earlier, as this logic doesn’t strictly go down to the day level. So, retail environments which have custom trading months, such as 26th – 25th, are not currently supported by the time calculation functions.