2nd Slicer value dependent on 1st Custom Date Slicer

[Anonymous]

Hi all! Long term lurker here. Can someone help me on this one?

I am building a dashboard using this logic. 


I have 2 slicers, Primary and Secondary. 
Primary Slicer has custom date values (Last 7 Days, Last Month, Month to Date and Year to Date)
Secondary Slicer has 3 values (Month on Month, Year on Year, Week on Week)

The primary slicer only filters the primary date range card. The field I used in the Primary Date Range Card is just the sum of meta scores (measure is included below).
The secondary slicer only filters the secondary date range card.

For example:
Today is Sept 01, 2023. 

If I select "Last 7 Days" on the Primary Slicer, I am expecting that the Primary Date Range Card should display values from Aug 26 - Sept 01 2023.  Same goes if I select "Last Month" on the Primary Slicer, I am expecting that the Primary Date Range Card should display values from Aug 01 - Sept 01 2023. 

Here is the problem. I am having an issue because I do not know how to implement the Secondary Slicer using DAX Query only.

If I select "Last 7 Days" on the Primary Slicer, then I select "Month on Month" on Secondary Slicer, the expectation should be that the Secondary Date Range Card will display values from July 26 - Aug 26 2023. If I select "Year on Year", expectation is that the card will display values from Aug 26 - Sept 01 2022.

This is the measure I used for calculating the value used in the Primary Date Range Card:

_PrimaryCalculation =

VAR PeriodSelected = SELECTEDVALUE(Param_Primary[ID])

VAR _last7days = CALCULATE([SwitchMetaScore],DATESINPERIOD(NintendoGames[date],MAX(NintendoGames[date]),-7,DAY))

VAR _mtd = TOTALMTD([SwitchMetaScore],NintendoGames[date])

VAR _lastMonth = CALCULATE([SwitchMetaScore],DATESINPERIOD(NintendoGames[date],MAX(NintendoGames[date]),-1,MONTH))

VAR _ytd = TOTALYTD([SwitchMetaScore],NintendoGames[date])

Return

INT(

SWITCH(

    PeriodSelected,1,_last7days

                ,2,_mtd

                ,3,_lastMonth

                ,4,_ytd

)

)

And this is the measure I used for SwitchMetaScore:

SwitchMetaScore = CALCULATE(

sum(NintendoGames[meta_score]),filter(NintendoGames,NintendoGames[platform] = "Switch")

)

Now, what I need to accomplish is to capture the value for the Secondary Date Range using DAX measure. The values from the Primary and Secondary Cards will be used for multiple visuals such as Donut Charts and Line Graphs. 

Is it possible to get the Secondary Date Range value using the logic I used for getting the Primary Date Range value? Badly need some help here. Thank you very much!

[Anonymous]

Hi @amitchandak ! 

Let me add some details to my question. 

The fields that I used for Primary Slicer comes from param_Primary table. Same goes with Secondary Slicer. 

What I am trying to achieve is to filter my Secondary Slicer based on the selection I picked on the Primary Slicer.

Example 1:
Today is Sept 02, 2023.
1. If I pick "Last 7 Days" on Primary Date Slicer, the expected value on Primary Slicer should be sum of sales for the last 7 days (Aug 27, 2023 - Sept 02, 2023).
2. If I pick "Month on Month" on Secondary Slicer, the expected value on Secondary Slicer should be sum of sales for the month before. (July 27, 2023 - Aug 02, 2023).

Example 2:
Today is Sept 02, 2023.
1. If I pick "Last Month" on Primary Date Slicer, the expected value on Primary Slicer should be sum of sales for the last month (Aug 01, 2023 - Aug 31, 2023).
2. If I pick "Month on Month" on Secondary Slicer, the expected value on Secondary Slicer should be sum of sales for the month before. (Jul 01, 2023 - Jul 31, 2023)

Example 3:
Today is Sept 02, 2023.
1. If I pick "YTD" on Primary Date Slicer, the expected value on Primary Slicer should be sum of sales from Jan 01, 2023 - Sept 02, 2023).
2. If I pick "Year on Year" on Secondary Slicer, the expected value on Secondary Slicer should be sum of sales for the year before the date on Primary Slicer. (Jan 01, 2022 - Sept 02, 2023).

I have included the sample I have created. Please refer to link below. Hope everything clears up. Thanks! 
2 Date Ranges.pbix 

[amitchandak]

@Anonymous , All the function which you have used like dateinperiod, datemtd etc give table , You can create a measure like

Date = minx(DATESINPERIOD(NintendoGames[date],MAX(NintendoGames[date]),-7,DAY), [Date]) & " to  " & maxx(DATESINPERIOD(NintendoGames[date],MAX(NintendoGames[date]),-7,DAY), [Date])

Use the same switch logic and return date ranges as text. If needed use format function

format(minx(DATESINPERIOD(NintendoGames[date],MAX(NintendoGames[date]),-7,DAY), [Date]), "MMM-YYYY")  to get month year