cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pat1214 Frequent Visitor
Frequent Visitor

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.

 

sample.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Variance DAX

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.

16.JPG

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Variance DAX

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.

16.JPG

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pat1214 Frequent Visitor
Frequent Visitor

Re: Variance DAX

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