Time Intelligence Trick in Power BI
One of my favorite features in a Dashboard is the ability to travel in time seamlessly. When a data model lacks time intelligence, I tend to lose interest in the Dashboard.
Whether you are tracking sales growth, monitoring inventory levels, or predicting market trends, Time Intelligence in Power BI empowers you to navigate your chronological data with unparalleled agility and insight.
There are different ways to set up Time Intelligence in Power BI:
- Calculation groups: I actually wrote an article about it:
- DAX Time Intelligence Functions: DAX language includes a suite of time intelligence functions like TOTALYTD (Total Year-to-Date), SAMEPERIODLASTYEAR, DATEADD, and many more. These functions allow you to perform time-based calculations easily.
- Quick Measures: For users less familiar with DAX, quick measures in Power BI provide pre-defined calculations, many of which are geared towards time intelligence, like calculating year-over-year change.
- Custom Calendar Tables using Power Query: this is more intuitive to build and requires less knowledge of DAX.
- Relative Date Filtering: Power BI allows you to filter data on your reports and dashboards dynamically using relative date filtering, such as the last N days, weeks, months, or years.
- Time-Based Slicers: Incorporating slicers based on time periods (like years, quarters, months, days) allows end-users to dynamically interact with the data and view it for different time frames without altering the underlying reports.
- Disconnected table: this approach has different strategies depending on how your data model has been built.
Disconnected table approach
In this article I will show you a trick that you can use to set up a quick and dirty time intelligence for your dashboards.
Step #1:
Add a disconnected table to your data model containing the Time bucket and an ID column based on the requirements of your dashboard. In this example we are going to create 4 measures only, but you can add as many as you need.
Step #2:
Copy the following DAX measure and add/remove the variables as per your requirements:
PeriodSelection =
VAR _PeriodSelected = SELECTEDVALUE('Time Intelligence'[ID])
VAR _MTD = MAX(FactTable[Date]) IN DATESMTD(DateTable[Date]) //Month to Date
VAR _QTD = MAX(FactTable[Date]) IN DATESQTD(DateTable[Date]) //Quater to Date
VAR _CYTD = MAX(FactTable[Date]) IN DATESYTD(DateTable[Date],"12/31") //Calendar Year to Date. In case your fiscal year starts on Jan 1st
VAR _FYTD = MAX(FactTable[Date]) IN DATESYTD(DateTable[Date],"6/30") //Calendar Year to Date. In case your fiscal year starts on Jul 1st
RETURN
INT(
SWITCH(
_PeriodSelected,
1,_MTD,
2,_QTD,
3,_CYTD,
4, _FYTD
)
)
Step #3:
Add the measure from step #2 to the visual you want to control and default the output to 1.
The magic happens when you select of the items from the disconnected table. The entire result is wrapped in the INT function, which converts the boolean TRUE/FALSE values from the time period checks into integer 1 or 0. This is likely for easier use or interpretation in whatever context this measure is used.
In summary, PeriodSelection lets a user select a time period and then calculates whether the latest date in your data falls within that selected period, returning 1 if it does and 0 if it doesn't.
You can also add the measure to other visuals if needed.
Here is the end result:
Pretty Cool, huh? simple and delivers a lot of insights with a few clicks.
If you want to play around with this report, you can download it using this link.
I hope you enjoyed reading this article. Thanks for reading and happy dashboarding!
PBI Guy
