How to use DAX expression to calculate count members based on Slicer (comparison period)

[Anonymous]

I have a requirement that need two slicers to calculate member. These slicers are Relative Period and Comparison Period

When I select Prior Week, It filtered through and my figures are correct and the date card visual shows the prior week date for accuracy. However, my requirement is to use Comparison Period Slicer to filter through the result  or visual when value in slicer is selected. This is what I'm struggling with. 
For example, when prior week is selected the date is 14 August 2021 and member is 4333, hence, when the Same Date(s), 1 Week Earlier is selected in Comparison Period, the date will be 7 August 2021 and measure will change. This is where I'm stuck.

How can I resolve this problem.

I have attached a link below where a dummy file resides.

https://drive.google.com/file/d/19_YNPz_SDqiEr7xt5j9GLfYmksh-ZuiZ/view?usp=sharing 

[Anonymous]

Hi All, has anyone has idea yet to my issue please

[Anonymous]

I have tried using the dax below but it is not working

Measure2 =

VAR ComparisonPeriod = SELECTEDVALUE('Comparison Period'[Comparison Period])

//VAR ComparisonTypeID = SELECTEDVALUE('Comparison Period '[TypeID])

VAR MinDateCal = MINX (ALLSELECTED(Datel),Date[Date])

VAR MinDateRP = MINX (ALLSELECTED('Relative Period),'Relative Period'[Date])

VAR MaxDateCal = MAXX (ALLSELECTED(Date),Date[Date])

VAR MaxDateRP = MAXX (ALLSELECTED('Relative Period’),'Relative Period’[Date])

VAR MINDATE = IF ( MinDateCal > MinDateRP , MinDateCal , MinDateRP )

VAR MAXDATE = IF ( MaxDateCal < MaxDateRP , MaxDateCal , MaxDateRP )

VAR Test = calculate ( DATEDIFF( MINDATE , MAXDATE , DAY ) , ALLSELECTED(Date) ) +1

RETURN

SWITCH(TRUE(),

    ComparisonPeriod = "Same Date(s), 1 Week Earlier", CALCULATE([No of members joined], DATEADD(Date[Date],-7,DAY)),

    ComparisonPeriod = "Same Date(s), 2 Weeks Earlier", CALCULATE([No of members joined], DATEADD(Date[Date],-14,DAY)),

    ComparisonPeriod = "Same Date(s), 3 Weeks Earlier", CALCULATE([No of members joined], DATEADD(Date[Date],-21,DAY)),

    ComparisonPeriod = "Same Date(s), 4 Weeks Earlier", CALCULATE(([No of members joined], DATEADD(Date[Date],-28,DAY)),

    ComparisonPeriod = "Same Date(s), 8 Weeks Earlier", CALCULATE(([No of members joined], DATEADD(Date[Date],-56,DAY)),

    ComparisonPeriod = "Same Date(s), 12 Weeks Earlier", CALCULATE(([No of members joined], DATEADD(Date[Date],-84,DAY)),

   /* ComparisonPeriod = "Year on Year", 0,

    ComparisonPeriod = "Preceeding 2 Weeks (Run In)", 0,

    ComparisonPeriod = "Preceeding 4 Weeks (Run In)", 0,

    ComparisonPeriod = "Preceeding 8 Weeks (Run In)", 0,

    ComparisonPeriod = "Preceeding 12 Weeks (Run In)", 0,

    ComparisonPeriod = "Same Date(s), Prior Cal Month", 0,

    ComparisonPeriod = "Same Date(s), Prior Quarter", 0,

    ComparisonPeriod = "Preceeding Complete Quarter", 0,

    ComparisonPeriod = "Preceeding Complete Cal Month", 0,*/

    BLANK()

)

Please can someone help me

[amitchandak]

@Anonymous , based in what I got so far

Let say for week we have column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

then measures
measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Last to last week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-2))

2 week before last week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])-1))

[Anonymous]

Thanks for your reply. However, the measure do not convey what i was looking for. Do you have access to the link i shared for a dummy pbix