Date Slicer Based on Calculated Columns

[stevie_westside]

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/False)
Current Month - (True/False)
Last Month - (True/False)
etc,etc....

I have been attempting this off an on for awhile and cannot seem to come up with a solution for each of these columns.
Ideally, I'd be able to create a slicer order column that says something like:
IF('Dates'[Today] = TRUE, 1
IF('Dates'[Previous Business Day] = TRUE, 2
IF('Dates'[This Week] = TRUE, 3
IF('Dates'[Last Week] = TRUE, 4
IF('Dates'[This Month] = TRUE, 5
etc, etc.... 

However, [Today] overlaps [This Week], so both are true.
[This Week] overlaps [This Month], and so on.... So trying to create a slicer order column doesn't exactly work.

I feel like I should be able to use SWITCH() in some capacity, but I'm at a loss.  Can some help me out here and put a solution to my constant head banging?  Thanks so much!

[stevie_westside · Accepted solution]

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. 

[stevie_westside]

Perhaps the best way to do this... and I'm spitballing here... rather than create 100s of measures, I can create a few measures with the other measures contained in VAR
i.e.
Quarter Measure =
var QTD = qtd(sales)
var QTDlastyear = sameperiodlastyear(QTD)
etc... 
and then some how create switch statement in the RETURN

[khaledHabib90]

that might be a way, you can also use field parameters. or consider calculation groups if the calculations will be done in a table view

[stevie_westside]

Essentially, I want the Slicer to have the choices stated above...
Today, PrevBizday, This Week, Last Week, etc...
This Slicer will be placed on 2 different reports...
One will be comparative analysis... i.e. "How I'm I doing this week in Sales...."  vs "Hmm, where were we at last year"
the other report is KPI based.  We have daily targets to meet, as well as weekly, monthly, quarterly, etc.  Having a slicer to switch views quickly was the end goal.

I'm open to suggestions.  From an effeciency standpoint, I thought the slicer would be far more effecient than trying to create hundreds of measures for things like SAMEPERIODLASTYEAR, and MTD, QTD, YTD, etc.

[stevie_westside]

The slicer is for the C-Suite big wigs that don't want to have the choices or understanding of a relative date slicer.  They want to see a drop down with the aforementioned selections.
I am also going to tell the future here and bet they are just going to ask for different report pages that show only "this week","this month", "this quarter", in which case the slicer then benefits me from an efficiency standpoint

[khaledHabib90]

Okay so if i am understanding correctly a Switch statement should do the job since it stops once a consition is true

So it should be something like this

Switch(true(),

'Dates'[Today] = TRUE, 1,

'Dates'[Previous Business Day] = TRUE, 2,

'Dates'[This Week] = TRUE, 3,

'Dates'[Last Week] = TRUE, 4,

'Dates'[This Month] = TRUE, 5

Etc..

Did my reply help you solve the problem? Please accept it as the solution of it does 🙂

[stevie_westside]

Same issue as always.... overlap.  So in this case,
'Today' doesn't exist, nor does 'Current Month'
'CurrentWeek' shows only 1 Day (because it's Wednesday yesterday (prevbizday) is already taken leaving room for just 1 day to be flagged.... so on an so forth.  Been down this road... I just don't know a viable solution.

[khaledHabib90]

I think I am confused about your end goal, 

so when a day meets multiple conditions like Wednesday. what do you want to achieve in this date slicer ?

[khaledHabib90]

Hi stevie, i was wondering if you tried the relative date slicer option?, just Trying to understand your scenario a little bit more to help you