Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBrilliant, it did the trick! Thank you!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |