Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Extra MoM% computed

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)
)
 
 
Need Help
Tips and Tricks
  • Kishore_KVN's avatar
    Kishore_KVN
    2 years ago

    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!!

1 Reply

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    2 years ago

    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!!