Forum Discussion
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)
PLEASE HELP !!!
Thanks
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.
Regards,
Frank
2 Replies
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.
Regards,
Frank- Thanks ! this works great :)
