Dynamic performance % dax formula

0
votes

Hope you all are doing well and safe!!

I have a question regarding Power BI dax. I want to create a dax measure for performance percentage in which numerator and denominator will be as per user filter.

Case 1: I want to create a dax measure for performance % (Current Month Sales / Dec'20 Sales) in which current month value will be depending upon user drop down filter and denominator will be fixed with Dec'20 sales value.

Case 2: I want to create a dax measure for performance % (Current Month Sales / Previous Month Sales) in which current month value will be depending upon user drop down filter and denominator will be as per previous month depending upon month selection done by user.

Thanks in advance :)

1
dynamicpowerbidax

1 Answers

0
votes

Try these measures

Case 1 =
DIVIDE (
    [Measure],
    CALCULATE (
        [Measure],
        Dates[Calendar Year Number] = 2020,
        Dates[Month Number] = 12,
        REMOVEFILTERS ( Dates )
    )
)

Case 2 =
VAR SelectedMonth =
    CALCULATE ( MAX ( Dates[Calendar Year Month Number] ), ALLSELECTED ( Dates ) )
VAR PreviousMonth =
    CALCULATE (
        MAX ( Dates[Calendar Year Month Number] ),
        Dates[Calendar Year Month Number] < SelectedMonth,
        REMOVEFILTERS ( Dates )
    )
VAR Result =
    DIVIDE (
        [Measure],
        CALCULATE (
            [Measure],
            Dates[Calendar Year Month Number] = PreviousMonth,
            REMOVEFILTERS ( Dates )
        )
    )
RETURN
    Result