DAX Time Intelligent – Single Measure For Multiple Dates

DAX Time Intelligent – Single Measure For Multiple Dates

Let’s assume a scenario where we have 50 time intelligent measures and 5 dimension Dates (order, invoice, ship, due, calendar…).
If we want the 50 measure to interact with the 5 dimensions we would need to create 250 measures which might rapidly overwhelm the users as they must sift though so many measures.

This script below allows you to drag any dates with the YTD margin measure and the measure will dynamically apply the calculation accordingly and thus keeps our number of measures to 50 instead of 250.

Total Margin YTD:=
var AllDate = COUNTROWS(all('Order Date'))
--you can use any date dimension as long as they have the same number of rows
--you can also create a separate measure for the count all to avoid
--recomputing and retyping the var calculation each time
return
switch(
TRUE(),
COUNTROWS('Order Date')<AllDate,TOTALYTD([Internet Total Margin],'Order Date'[Order Date]) ,
COUNTROWS('Due Date')<AllDate,TOTALYTD([Internet Total Margin],'Due Date'[Due Date]),
COUNTROWS('Ship Date')<AllDate,TOTALYTD([Internet Total Margin],'Ship Date'[Ship Date]),
TOTALYTD([Internet Total Margin],'Order Date'[Order Date])
)   

Note that you can also use the function IsCrossFIltered(datekey) instead of CountRows() but you cannot use IsFiltered() or HasOneFiltered() as it returns TRUE only when a column is being filtered directly.
So far I seem to have just a little bit better performance with the countrows() version but I haven’t really tested it on a large database.

The main drawback of it is that we need to create many role-playing dimension instead of keeping only one dimension and taking advantage of the UseRelationship DAX function. But role-playing date dimensions are very small so memory consumption won’t be an issue anyway!

Here I use the same measure with three different dates. (AdventureWorks2016 sample data)
Capture

 

This technique might become useless once the new “calculation group”  feature of SSAS 2019 will be released.

 

Leave a Reply

Your email address will not be published. Required fields are marked *