cancel
Showing results for
Search instead for
Did you mean:
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.

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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

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

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.
Frequent Visitor

## Re: Variance DAX

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