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.
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?
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.
Is it possible for you to send me the PBIX file please?
Hi Brad,
you can download the .pbix from this link:
https://1drv.ms/u/s!Ajv71xAlriwysUsye_N4SOlGpu94
Good luck,
Pawel
Hello, is it possible to have the PBIX file? You sent the SalesRepScore report to the last person asking for it (very interresting report to learn by the way).
Have a good day.
Cordially, Max
Hey hi, can you please share the Power BI Dynamic Periods Power BI file
Pbix file please