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"
FORMAT( vFirstDate, vDateFormat ) & UNICHAR(10) &
FORMAT( vLastDate,  vDateFormat ) & " x" & FORMAT( vNumDays, "#,##0" )
Brian Grant

Brian Grant

Brian Grant is a masterful Senior Analytics Consultant by day, and a Power BI legend by night. A passion for Power BI drove him to holistically master the tool from all three perspectives: M, DAX, and the visualization layer. Brian is a constant teacher who leads Power BI training sessions, while sharing his wealth of expertise through video and blogging resources.
Share on twitter
Share on facebook
Share on linkedin
Share on email


Subscribe to CSG. We’ll supply your inbox with the latest blog posts, training videos, and upcoming events.

Ready to wrangle your data?