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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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!

2 ACCEPTED SOLUTIONS
MFelix
Super User
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:

 

dd.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
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:

 

dd.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Brilliant, it did the trick!  Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.