Extra MoM% computed

[Anonymous]

I calculated the MoM% with the "Quick Measure" option. The problem is that it adds an extra month that doesn't exist or assumes that the sales are zero in the next month. For example, I have data only until January 2024 and the used MoM% computes for February 2024 also (assumes that sales for February 2024 are zero then the MoM% this month is -100%). I would like to modify the measure to compute MoM% only until January 2024. I've tried a bunch of alternatives but nothing worked.

Sales MoM% =

IF(

    ISFILTERED('Sheet1'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH = CALCULATE(

        SUM('Sheet1'[Sales]),

        DATEADD('Sheet1'[Date].[Date], -1, MONTH)

    )

    RETURN

            DIVIDE(SUM('Sheet1'[Sales]) - __PREV_MONTH, __PREV_MONTH)

)

[Kishore_KVN · Accepted solution]

Hello @Anonymous , Please use below measure and it will remove current month. 

Sales MoM% =
IF(
    ISFILTERED('Sheet1'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_MONTH = CALCULATE(
        SUM('Sheet1'[Sales]),
        DATEADD('Sheet1'[Date].[Date], -1, MONTH)
    )
    RETURN
            IF(SUM('Sheet1'[Sales]) <> BLANK(),DIVIDE(SUM('Sheet1'[Sales]) - __PREV_MONTH, __PREV_MONTH),BLANK())
)

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!