Dynamic Month Over Month with filters

[J_Mug]

Hi, 

I wanted to know if it's possible to have a dynamic calculation of MoM/YoY % to change with date filters. Right now I have this measure and it returns correctly the current month change vs the previous one but it's static so it will only get me the MoM% vs the last month of the calendar (right now March vs April).

Response MoM%- =

VAR __BASELINE_VALUE = CALCULATE(TOTALMTD(COUNT(NPS'[Ratings]);ALLSELECTED(Calendary[Date]);ALLSELECTED(Calendary[Date]));DATEADD(Calendary[Date];-1,;MONTH))

VAR __VALUE_TO_COMPARE = TOTALMTD(COUNT(NPS'[Ratings]);ALLSELECTED(Calendary[Date]);ALLSELECTED(Calendary[Date]))

RETURN

    IF(__VALUE_TO_COMPARE > __BASELINE_VALUE;ABS(DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE; __BASELINE_VALUE;0));-ABS(DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE; __BASELINE_VALUE;0)))

I wonder if I can get it to work so that if an user filters by the month of january, the measure would recognize that and so the month to date would be for january and the adddate -1 would calculate december.

Kind regards,

[v-yingjl]

Hi @J_Mug ,

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,

Yingjie Li

[v-yingjl · Accepted solution]

Hi @J_Mug ,

I don't know the structure of your dataset but the logic of the measure should work fine. Have you tested the measure in your report?

Or you can modify the measure like this but I'm not certain:

Response MoM% =
VAR _BASELINE_VALUE =
    CALCULATE (
        COUNT ( NPS[Ratings] ),
        DATESMTD ( DATEADD ( Calendary[Date], -1, MONTH ) )
    )
VAR _VALUE_TO_COMPARE =
    TOTALMTD ( COUNT ( NPS[Ratings] ), ALLSELECTED ( Calendary[Date] ) )
RETURN
    IF (
        _VALUE_TO_COMPARE > _BASELINE_VALUE,
        ABS ( DIVIDE ( _VALUE_TO_COMPARE - _BASELINE_VALUE, _BASELINE_VALUE, 0 ) ),
        - ABS ( DIVIDE ( _VALUE_TO_COMPARE - _BASELINE_VALUE, _BASELINE_VALUE, 0 ) )
    )

If the above not help, could you please share a sample file for further discussion? Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@J_Mug it should work , did you tested it, what is not working?