Forum Discussion

pat1214's avatar
pat1214
Frequent Visitor
7 years ago
Solved

Variance DAX

Hello,   I am just starting to build dashboards in PowerBI. Can someone please review the DAX I have used and help me with modifying it for my needs please? I am trying to find a way to calculate v...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, pat1214

    After my test, you could this formula as below:

    Sum of KPI values for (Oct '18 to Oct '17)

    Sum of KPI values for (Oct '18 to Oct '17) = 
CALCULATE (
    SUM ( Episodes[KPI_VALUE] ),
    FILTER (
        Episodes,
        DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) > 0
            && DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) <= 13
    )
)

    Sum of KPI values for (Oct '17 to Oct '16)

    Sum of KPI values for (Oct '17 to Oct '16) = 
CALCULATE (
    SUM ( Episodes[KPI_VALUE] ),
    FILTER (
        Episodes,
        DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) > 13
            && DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) <= 25
    )
)

    this variance in percentage

    this variance in percentage = 
DIVIDE (
    ( [Sum of KPI values for (Oct '18 to Oct '17)]
        - [Sum of KPI values for (Oct '17 to Oct '16)] ),
    [Sum of KPI values for (Oct '17 to Oct '16)],
    0
)

    Result:

    for METRIC_ID is 1 by your sample data.

     

    Best Regards,
    Lin

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, pat1214

After my test, you could this formula as below:

Sum of KPI values for (Oct '18 to Oct '17)

Sum of KPI values for (Oct '18 to Oct '17) = 
CALCULATE (
    SUM ( Episodes[KPI_VALUE] ),
    FILTER (
        Episodes,
        DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) > 0
            && DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) <= 13
    )
)

Sum of KPI values for (Oct '17 to Oct '16)

Sum of KPI values for (Oct '17 to Oct '16) = 
CALCULATE (
    SUM ( Episodes[KPI_VALUE] ),
    FILTER (
        Episodes,
        DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) > 13
            && DATEDIFF ( Episodes[REPORTING_PERIOD], TODAY (), MONTH ) <= 25
    )
)

this variance in percentage

this variance in percentage = 
DIVIDE (
    ( [Sum of KPI values for (Oct '18 to Oct '17)]
        - [Sum of KPI values for (Oct '17 to Oct '16)] ),
    [Sum of KPI values for (Oct '17 to Oct '16)],
    0
)

Result:

for METRIC_ID is 1 by your sample data.

 

Best Regards,
Lin

 

  • pat1214's avatar
    pat1214
    Frequent Visitor
    7 years ago

    Thank you so much! This worked for my needs. I appreciate your help a lot!