Reply
Frequent Visitor
Posts: 4
Registered: ‎04-09-2018
Accepted Solution

Year 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_ CalendarYearShipmentsVolumeEUsSamePeriodLastYearYOY (vs. LY)YOY %
01/07/20153405067.395   
01/08/20159035592.39   
01/09/20156870603.54   
01/10/20156319077.838   
01/11/20155804231.065   
01/12/20155738833.616   
01/01/20164215018.789   
01/02/20167416320.188   
01/03/20161924490.596   
01/04/20166813610.669   
01/05/20164182010.446   
01/06/20163469226.412   
01/07/20167991749.623405067.3954586682.225235%
01/08/20166531696.4919035592.39-2503895.89972%
01/09/20162791788.2776870603.54-4078815.26341%
01/10/20165666541.4786319077.838-652536.360190%
01/11/20164959801.9755804231.065-844429.089885%
01/12/20164656157.7715738833.616-1082675.84581%
01/01/20178746682.5074215018.7894531663.717208%
01/02/20179280674.6957416320.1881864354.506125%
01/03/20171046431.211924490.596-878059.385954%

 

Any tips how I can do that?

 

Thank you!


Accepted Solutions
Super User
Posts: 1,413
Registered: ‎09-19-2016

Re: Year on Year Calculations

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:

 

dd.png

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Posts: 1,413
Registered: ‎09-19-2016

Re: Year on Year Calculations

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post


All Replies
Super User
Posts: 1,413
Registered: ‎09-19-2016

Re: Year on Year Calculations

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:

 

dd.png

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Frequent Visitor
Posts: 4
Registered: ‎04-09-2018

Re: Year on Year Calculations

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!

Super User
Posts: 1,413
Registered: ‎09-19-2016

Re: Year on Year Calculations

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Frequent Visitor
Posts: 4
Registered: ‎04-09-2018

Re: Year on Year Calculations

Brilliant, it did the trick!  Thank you!