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.

4 thoughts on “Power BI Dynamic Time Selector

  1. Lee Reply

    You seem to be making the assumption that every period is based on a calendar year when in fact it’s more arbitrary. Thousands of companies have Fiscal, Benefit, and other “years” based on something other than Jan – Dec. And if you are using PowerBI to examine multiple customer’s data you need to somehow be able to specify what the start and end points are for these time periods and somehow work off of that. Any ideas?

    • pawel Post authorReply

      Right, this report is for a calendar year (Jan-Dec). In order to run this for a ‘non-standard’ fiscal year (eg. Jul-Jun), you need to add ‘Fiscal Year’, ‘Fiscal Quarter’, etc columns in the Calendar dimension table and adjust the YTD functions in DAX with ’06/30′ as the fiscal period end.

  2. Brad Zipursky Reply

    Is it possible for you to send me the PBIX file please?

Leave a Reply

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