DAX MoM, YoY and slicer

[Anonymous]

Hi all,

So far, I have built two measure as the following, connected them to a slicer with Switch statement:

- MoM: This takes max in database month figures, compare it with last month (max_in_db-1).

- YoY: This take max in database month figure, compare it with the same month last year.

I have added both of them to the same card, with a switch statement reading from a slicer. Everything is working perfectly. 

However, I have been asked to add date filter where users can select any date period and ignore the MoM and YoY so they filter the rest of the results based on that date sliecer, and no need to calculate MoM or YoY on the go. Whenever users selects any period, the measures don't work (so if they selected 10 days period from date filter, all report will be filtered in that date period)

I added another switch value called filter, what I need now is:

 - Whenever business user clicks on MoM slicer, the date filter slicer should have the current month and last month period (so the MoM measure works).

- The same goes for YoY where date filter range should be from Last year same month, till this year current month so YoY works.
- Otherwise, all date values should be in Date filter.

Appreicate your kind responce, I attached my template here.

P.s: As a workaround, I added two columns (From/To) where date value is being fed through DAX, but this is not filtering correctly the result although Date Slicer is filtered. 

Regards,

[v-yalanwu-msft]

Hi， @Anonymous ；

What I understand is that your MoM and YoY data change by the date slicer? I took a look at your file, yours is valid; as shown in the figure:

But your date range is too large, I suggest you use'Dynamics RequestHeader'[createdon] as a date slicer, perhaps better:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Based on the date slicer, say slicer is on a date tbale

example 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,Year)))

You can use Slicer and Switch statement based weather you want use max date or Date slicer and use that.

[Anonymous]

Thank you so much @amitchandak  for your prompt response. Actually, my issue is not in calculating the measures as I have done it. Whenever I click on Month, Year slicer (as in my template) the measures will be presented based on the switch. This is working.

However, what I want is when I choose the date period from data slicer, I want the measures to be recalculted based on that date slicer (I have added in my template another button along with Month and Year for the date slicer, I called it filter)

Here is my template