Creating and troubleshooting time intelligence measures in Power BI can be tricky work. Getting a measure to work at one granularity is hard enough, but when you start to look at rollup values it’s difficult to be confident that you’re getting the correct numbers. It’d be great to get a look at the date table you’re passing into calculate to see if it indeed contains the days that it should for each place that it gets used.
In this video we’ll be building a time intelligence inspector that will let us peer inside any of our time intelligence measure and for our constructed tables, show us the first and last date, as well as the number of days it has. This will enable us to quickly spot check the values in our matrix to see if the days our measure is considering match up to the time intelligence question being asked. This technique can be used for all kinds of complex DAX measures, but for time intelligence measures in particular it can be invaluable.
The Code:
New Inspector =
VAR vFirstDate = MIN( DimDate[Date] )
VAR vLastDate = MAX( DimDate[Date] )
VAR vNumDays = COUNTROWS( DimDate )
VAR vDateFormat = "DD MMM YYYY"
RETURN
FORMAT( vFirstDate, vDateFormat ) & UNICHAR(10) &
FORMAT( vLastDate, vDateFormat ) & " x" & FORMAT( vNumDays, "#,##0" )
