Forum Discussion

stevie_westside's avatar
2 years ago
Solved

Date Slicer Based on Calculated Columns

I have a date table. Within this date table I have several calculated columns (boolean). Today - (True/False) Previous Business Day - (True/False) Current Week - (True/False) Last Week - (True/F...
  • stevie_westside's avatar
    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.