How to create a Power BI Dynamic Time Selector using DAX?
Power BI – or DAX to be precise – gives you amazing possibilities for setting desired time frame for your analysis. Whether Year-to-Date, Month-To-Date, Last Week, Last Month, 12 month moving totals, Cumulative, compared to previous year or to budget (…), all you need to do is to write a DAX measure and place a visual in a report page. Not a big deal. You can learn it at our Power BI course for beginners.
Sometimes, to observe short- and long term trends, a business user wants to switch from one period to another, swiftly and without turning to another page. With such a business user in mind, I created this page containing DYNAMIC TIME SELECTOR (or Time Filter). Use the TOGGLE to switch between PERIOD (YTD, QTD, MTD) and SLICER (any period selection) views.
I used this nested DAX formula to generate periods:
Period Sales =
VAR PeriodSelected = SELECTEDVALUE(Periods[Period])
RETURN
SWITCH(TRUE(),
PeriodSelected = „MTD“, [Sales MTD],
PeriodSelected = „QTD“, [Sales QTD],
PeriodSelected = „YTD“, [Sales YTD],
PeriodSelected = „Cum“,[Cumulative]
)
I used BOOKMARK function to switch between Period and Slicer views.
Here the report:
Let me know if you have any questions or comments.