Forum Discussion
Helper VYear on Year Calculations
Hi all
I'm getting a bit stuck trying to figure out how to create a YOY calculation in actual numbers and as a %.
Below Example shows the first two columns which I have, and the three columns I am trying to generate.
| MonthName_ CalendarYear | ShipmentsVolumeEUs | SamePeriodLastYear | YOY (vs. LY) | YOY % |
| 01/07/2015 | 3405067.395 | |||
| 01/08/2015 | 9035592.39 | |||
| 01/09/2015 | 6870603.54 | |||
| 01/10/2015 | 6319077.838 | |||
| 01/11/2015 | 5804231.065 | |||
| 01/12/2015 | 5738833.616 | |||
| 01/01/2016 | 4215018.789 | |||
| 01/02/2016 | 7416320.188 | |||
| 01/03/2016 | 1924490.596 | |||
| 01/04/2016 | 6813610.669 | |||
| 01/05/2016 | 4182010.446 | |||
| 01/06/2016 | 3469226.412 | |||
| 01/07/2016 | 7991749.62 | 3405067.395 | 4586682.225 | 235% |
| 01/08/2016 | 6531696.491 | 9035592.39 | -2503895.899 | 72% |
| 01/09/2016 | 2791788.277 | 6870603.54 | -4078815.263 | 41% |
| 01/10/2016 | 5666541.478 | 6319077.838 | -652536.3601 | 90% |
| 01/11/2016 | 4959801.975 | 5804231.065 | -844429.0898 | 85% |
| 01/12/2016 | 4656157.771 | 5738833.616 | -1082675.845 | 81% |
| 01/01/2017 | 8746682.507 | 4215018.789 | 4531663.717 | 208% |
| 01/02/2017 | 9280674.695 | 7416320.188 | 1864354.506 | 125% |
| 01/03/2017 | 1046431.21 | 1924490.596 | -878059.3859 | 54% |
Any tips how I can do that?
Thank you!
Hi dapperscavenger,
Assuming you have a date table related with your main data add the following measures:
TotalMTD = TOTALMTD(SUM(Data[ShipmentsVolumeEUs]);DimDate[Date]) TotalMTDPY = TOTALMTD ( SUM ( Data[ShipmentsVolumeEUs] ); SAMEPERIODLASTYEAR ( DimDate[Date] ) ) YOY = IF ( [TotalMTDPY] = BLANK (); BLANK (); [TotalMTD] - [TotalMTDPY] ) YOY% = IF([TotalMTDPY] = BLANK();BLANK();[YOY]/[TotalMTDPY]+1)Last measure format as % final result below:
Regards,
MFelix
Hi dapperscavenger,
You need to have a date column on the measure something like this:
TotalMTDPY = TOTALMTD(SUM(FinalReport[ShipmentsVolumeEUs]),SAMEPERIODLASTYEAR(FinalReport[MonthYear]))
Do you have the column MonthYear as a date if so the above formula should work.
Regards,
MFelix
4 Replies
- MFelix
Super User
Hi dapperscavenger,
Assuming you have a date table related with your main data add the following measures:
TotalMTD = TOTALMTD(SUM(Data[ShipmentsVolumeEUs]);DimDate[Date]) TotalMTDPY = TOTALMTD ( SUM ( Data[ShipmentsVolumeEUs] ); SAMEPERIODLASTYEAR ( DimDate[Date] ) ) YOY = IF ( [TotalMTDPY] = BLANK (); BLANK (); [TotalMTD] - [TotalMTDPY] ) YOY% = IF([TotalMTDPY] = BLANK();BLANK();[YOY]/[TotalMTDPY]+1)Last measure format as % final result below:
Regards,
MFelix
dapperscavengerThanks MFelix
I can't quite get the second measure. I have:
TotalMTDPY = TOTALMTD(SUM(FinalReport[ShipmentsVolumeEUs]),SAMEPERIODLASTYEAR(FinalReport[MonthYear].[Date]))
What have I done wrong?
Thank you!
- MFelix
Hi dapperscavenger,
You need to have a date column on the measure something like this:
TotalMTDPY = TOTALMTD(SUM(FinalReport[ShipmentsVolumeEUs]),SAMEPERIODLASTYEAR(FinalReport[MonthYear]))
Do you have the column MonthYear as a date if so the above formula should work.
Regards,
MFelix