Power BI Dynamic Time Selector

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.

dynamic periods
Toggle Switch set on PERIODS
Dynamic Periods toggle switch
Toggle Switch set on SLICER

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.

Leave a Reply

Your email address will not be published. Required fields are marked *