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 variance between KPIs from past 12 months vs KPIs going back another 12 months. So essentially, it's something like this 

 

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)

 

I also need to display this variance in percentage.

 

This is the DAX I used 

 

CALCULATE(
CALCULATE(
SUM(
Episodes[KPI_VALUE]
)
,FILTER(
Episodes,Episodes[REPORTING_PERIOD].[Year]=(year(TODAY())-1)
)
)
-
CALCULATE(
SUM(
Episodes[KPI_VALUE]
)
,FILTER(
Episodes,Episodes[REPORTING_PERIOD].[Year]=year(TODAY())
)
)
) * -1

 

Here is a sample of the table that I have to use for this calculation. I have a separate column for each of the KPI (Matriculation Rate, App In Rate, etc) on my dashboard and I need to display the variance for each of the KPI.

 

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

     

2 Replies

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