Date Slicer Based on Calculated Columns
- 2 years ago
Okay, after trial and a lot of error I was able to do this using SWITCH, but in a completely different way than I initially imagined. I'm actually pumped that I got to learn it this way because this takes me in a new direction of thinking regarding dynamic date slicers in relation to measures.
Solution:
Step 1: Create a measure selection table:MeasureSelection = DATATABLE( "MeasureName", STRING, { {"WTD Shipments"}, {"MTD Shipments"}, {"QTD Shipments"}, {"YTD Shipments"}, {"MoM Change"}, {"MoM % Change"}, {"QoQ Change"}, {"QoQ % Change"}, {"YoY Change"}, {"YoY % Change"} } )Step 2: Create Measure with Variables outlining time series breakdown on a specific measure, in this case, I chose to use my [Shipments] measure:
Dynamic Shipments = //UPDATED VAR CurrentDate = CALCULATE( MAX('Dates'[Date]), FILTER( ALL('Dates'), 'Dates'[Date] = TODAY())) VAR PreviousBusinessDay = CALCULATE( MAX('Dates'[Date]), FILTER( ALL('Dates'), 'Dates'[Date] < TODAY() && 'Dates'[IsBusinessDay] = TRUE)) VAR CurrentWeekShipments = CALCULATE( [Shipments], FILTER( ALL('Dates'), 'Dates'[Date] >= (MAX('Dates'[Date]) - WEEKDAY(MAX('Dates'[Date]), 2) + 1) && 'Dates'[Date] <= MAX('Dates'[Date]))) VAR TodayDate = TODAY() VAR StartOfLastWeek = TodayDate - WEEKDAY(TodayDate, 2) - 6 VAR EndOfLastWeek = TodayDate - WEEKDAY(TodayDate, 2) VAR LastWeekShipments = CALCULATE( [Shipments], FILTER( ALL('Dates'), 'Dates'[Date] >= StartOfLastWeek && 'Dates'[Date] < EndOfLastWeek)) VAR PreviousBusinessDayShipments = CALCULATE([Shipments],'Dates'[Date] = PreviousBusinessDay) VAR CurrentMonthShipments = CALCULATE([Shipments], DATESMTD('Dates'[Date])) VAR PreviousMonthShipments = CALCULATE([Shipments], DATESMTD(DATEADD('Dates'[Date], -1, MONTH))) VAR CurrentQuarterShipments = CALCULATE([Shipments], DATESQTD('Dates'[Date])) VAR PreviousQuarterShipments = CALCULATE([Shipments], DATESQTD(DATEADD('Dates'[Date], -1, QUARTER))) VAR CurrentYearShipments = CALCULATE([Shipments], DATESYTD('Dates'[Date])) VAR PreviousYearShipments = CALCULATE([Shipments], DATESYTD(SAMEPERIODLASTYEAR('Dates'[Date]))) VAR MoMChange = CurrentMonthShipments - PreviousMonthShipments VAR MoMPercentChange = IF(PreviousMonthShipments = 0, BLANK(), MoMChange / PreviousMonthShipments) VAR QoQChange = CurrentQuarterShipments - PreviousQuarterShipments VAR QoQPercentChange = IF(PreviousQuarterShipments = 0, BLANK(), QoQChange / PreviousQuarterShipments) VAR YoYChange = CurrentYearShipments - PreviousYearShipments VAR YoYPercentChange = IF(PreviousYearShipments = 0, BLANK(), YoYChange / PreviousYearShipments) VAR SelectedMeasure = SELECTEDVALUE('MeasureSelection'[MeasureName]) // Adjust as needed for your measure selection mechanism RETURN SWITCH( SelectedMeasure, "Current Date", CurrentDate, "Previous Business Day", PreviousBusinessDayShipments, "WTD", CurrentWeekShipments, "PWTD", LastWeekShipments, "MTD", CurrentMonthShipments, "PMTD", PreviousMonthShipments, "QTD", CurrentQuarterShipments, "PQTD", PreviousQuarterShipments, "YTD", CurrentYearShipments, "PYTD", PreviousYearShipments, "MoM Change", MoMChange, "MoM % Change", MoMPercentChange, "QoQ Change", QoQChange, "QoQ % Change", QoQPercentChange, "YoY Change", YoYChange, "YoY % Change", YoYPercentChange, BLANK() )Step 3: Add a slicer to the canvas and drag the [MeasureName] from the 'MeasureSeleciton' table you created.
Step 4: Add Stacked Column Chart (or chart of your choosing) to the canvas. Add your Date field to the X-Axis and the Dyanmic Shipments Measure to the Y-Axis. You can now use the slicer to filter through the time series desired via the [Dynamic Shipments] measure.
Additional Step If Desired:
In order to make my chart more readable I opted for another slicer that allowed me to change the view of the chart based on day, week, month, quarter, and year. I simply created a field parameter via some calculated columns I created within my Date table:Date Graph Filter = { ("Date", NAMEOF('Dates'[Date]), 0), ("Week", NAMEOF('Dates'[WeekEnding]), 1), ("Month", NAMEOF('Dates'[MonthEnding]), 2), ("Quarter", NAMEOF('Dates'[QuarterEnding]), 3), ("Year", NAMEOF('Dates'[Year]), 4) }I added the fields through Power Query using M query language:
WeekEnding = Date.EndOfWeek([Date]) MonthEnding = Date.EndOfMonth([Date]) QuarterEnding = Date.EndOfQuarter([Date]) YearEnding = Date.EndOfYear([Date])
Eventually, I intend on building a master dynamic measure selection table that will allow me to add an additional slicer to the canvas. This slicer would allow me to sift through the measures (Dyanmic Measures like [Dyanmic Shipments], and switch between them via the slicer rather than having multiple pages and reports for each dynamic measure.
I hope this documentation helps someone in the future. This took me a bit to get the hang of.