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 Current...
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.

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🙏