Forum Discussion

jayceeb's avatar
jayceeb
Icon for Helper I rankHelper I
1 year ago
Solved

Month on Month variance percentage

I need to calculate the MoM variance percentage I already had a formula however whenever I select the date the first selected month shows 0.00% it should be not equal 0.00% it should be still CurrentVariance-PreviousVariance/PreviousVariance

 
 

 

Formula:

MOM% =
VAR CurrentMonth = MAX(DateFilter[Custom])
VAR PrevMonth =
    CALCULATE(
        MAX(DateFilter[Custom]),
        FILTER(
            ALL(DateFilter),
           DateFilter[Custom]< CurrentMonth
        )
    )
VAR CurrentVariance = [OB comp]
VAR PreviousVariance =
    CALCULATE(
        [OB comp],
       DateFilter[Custom] = PrevMonth
    )
RETURN
IF(
    ISBLANK(PreviousVariance),
    0,
    (CurrentVariance-PreviousVariance)/PreviousVariance
)

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jayceeb 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    Please try the DAX below. If you continue to experience any issues, feel free to reach out here. We are happy to assist you.

    MOM% =
    VAR CurrentMonth = MAX(DateFilter[Custom])
    VAR PrevMonth =
    CALCULATE(
    MAX(DateFilter[Custom]),
    FILTER(
    ALL(DateFilter),
    DateFilter[Custom] < CurrentMonth
    )
    )
    VAR CurrentVariance = [OB comp]
    VAR PreviousVariance =
    CALCULATE(
    [OB comp],
    FILTER(
    ALL(DateFilter),
    DateFilter[Custom] = PrevMonth
    )
    )
    RETURN
    IF(
    ISBLANK(PreviousVariance),
    BLANK(),
    DIVIDE(CurrentVariance - PreviousVariance, PreviousVariance)
    )


     Thanks.

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    jayceeb , Try using

     

    DAX
    MOM% =
    VAR CurrentMonth = MAX(DateFilter[Custom])
    VAR PrevMonth =
    CALCULATE(
    MAX(DateFilter[Custom]),
    FILTER(
    ALL(DateFilter),
    DateFilter[Custom] < CurrentMonth
    )
    )
    VAR CurrentVariance = [OB comp]
    VAR PreviousVariance =
    CALCULATE(
    [OB comp],
    DateFilter[Custom] = PrevMonth
    )
    RETURN
    IF(
    ISBLANK(PreviousVariance),
    BLANK(), // Or you can return CurrentVariance or any other value you find meaningful
    (CurrentVariance - PreviousVariance) / PreviousVariance
    )

    • jayceeb's avatar
      jayceeb
      Icon for Helper I rankHelper I
      1 year ago

      still 0.00%. if previous month is filtered the result should not be change as per below🙏

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jayceeb 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    Please try the DAX below. If you continue to experience any issues, feel free to reach out here. We are happy to assist you.

    MOM% =
    VAR CurrentMonth = MAX(DateFilter[Custom])
    VAR PrevMonth =
    CALCULATE(
    MAX(DateFilter[Custom]),
    FILTER(
    ALL(DateFilter),
    DateFilter[Custom] < CurrentMonth
    )
    )
    VAR CurrentVariance = [OB comp]
    VAR PreviousVariance =
    CALCULATE(
    [OB comp],
    FILTER(
    ALL(DateFilter),
    DateFilter[Custom] = PrevMonth
    )
    )
    RETURN
    IF(
    ISBLANK(PreviousVariance),
    BLANK(),
    DIVIDE(CurrentVariance - PreviousVariance, PreviousVariance)
    )


     Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi jayceeb 

      I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi jayceeb 

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.