Building a DAX Time Intelligence Inspector

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" )
Share This:
Twitter
Facebook
LinkedIn

Stay up to date with the latest customer data news, expert guidance, and resources.

Iteration and Error Handling in M Query

All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function.

More Resources

Trusted By Over 6 Million End Users​

Industry leaders and over 6 million end users choose SkyPoint as their comprehensive and compliant Modern Data Stack Platform. A Gold Certified Microsoft Partner, SkyPoint Cloud turns siloed customer data into connected customer experiences.