PowerPivot DAX + Financial Time?

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:

=TOTALYTD(SUM(FactSales[SalesAmount]), DimDate[DateKey])

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:

DAX Financial Time Calcs in action

DAX Financial Time Calcs in action

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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: