cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Year Over Year Percentage Calculation not working

Hi There,

I am trying to do a Year over Year calculation in power BI for the following data

 Period Total Production Variance Financial Year Identifier 12/31/2014 70 Prior Period 2 12/31/2015 90 Prior Period 12/31/2016 100 Current Period

can you help me get he year over year calc - current year - prior period /prior period  and current period - Prior period 2 / prior period 2

without hardcoding the values ??

I tried the quick measures it doesn't work in my table/

I also tried doing this myself it doesnt work

SumVariance = CALCULATE(SUM('Inventory Summary Data'[Total Production Variance]))

VarianceLastYear = Calculate(SUM('Inventory Summary Data'[Total Production Variance]) ,SAMEPERIODLASTYEAR('Inventory Summary Data'[Period].[Date]))

YoYVariance = if(SELECTEDVALUE('Inventory Summary Data'[Financial Year Identifier]) = "Current Period",[SumVariance]/[VarianceLastYear]-1)

Thanks

1 ACCEPTED SOLUTION
Community Support

Hi @motyagi,

Based on my test, we can get the values as you want the following steps.

1. Enter the data as you shared and create the measure as below:

`VarianceLastYear = Calculate(SUM('Inventory Summary Data'[Total Production Variance]) ,SAMEPERIODLASTYEAR('Inventory Summary Data'[Period]))`
`VarianceLast2Year = TOTALYTD(SUM('Inventory Summary Data'[Total Production Variance]),DATEADD('Inventory Summary Data'[Period],-2,YEAR))`
```YoYVariance = var
P =SELECTEDVALUE('Inventory Summary Data'[Financial Year Identifier])
return
IF(P = "Current Period",[SumVariance]/[VarianceLastYear]-1)```
```YoYVariance2 = var
P =SELECTEDVALUE('Inventory Summary Data'[Financial Year Identifier])
return
IF(P = "Current Period",[SumVariance]/[VarianceLast2Year]-1)```

2. Then we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/7n2b4u2sb7cd49m/Year%20Over%20Year%20Percentage%20Calculation%20not%20work...

Regards,
Frank

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

Hi @motyagi,

Based on my test, we can get the values as you want the following steps.

1. Enter the data as you shared and create the measure as below:

`VarianceLastYear = Calculate(SUM('Inventory Summary Data'[Total Production Variance]) ,SAMEPERIODLASTYEAR('Inventory Summary Data'[Period]))`
`VarianceLast2Year = TOTALYTD(SUM('Inventory Summary Data'[Total Production Variance]),DATEADD('Inventory Summary Data'[Period],-2,YEAR))`
```YoYVariance = var
P =SELECTEDVALUE('Inventory Summary Data'[Financial Year Identifier])
return
IF(P = "Current Period",[SumVariance]/[VarianceLastYear]-1)```
```YoYVariance2 = var
P =SELECTEDVALUE('Inventory Summary Data'[Financial Year Identifier])
return
IF(P = "Current Period",[SumVariance]/[VarianceLast2Year]-1)```

2. Then we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/7n2b4u2sb7cd49m/Year%20Over%20Year%20Percentage%20Calculation%20not%20work...

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I
Thanks ! this works great 🙂