Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
motyagi
Helper I
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 

PeriodTotal Production VarianceFinancial Year Identifier
12/31/201470Prior Period 2
12/31/201590Prior Period
12/31/2016100Current 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

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
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.

image002 (1).png

 

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.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
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.

image002 (1).png

 

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.

Thanks ! this works great 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.