Year on Year Calculations

[dapperscavenger]

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!

[MFelix · Accepted 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

[MFelix · Accepted 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

[dapperscavenger]

Thanks @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 · Accepted solution]

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

[dapperscavenger]

Brilliant, it did the trick!  Thank you!