Forum Discussion

dapperscavenger's avatar
dapperscavenger
Icon for Helper V rankHelper V
8 years ago
Solved

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...
  • MFelix's avatar
    MFelix
    8 years ago

    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's avatar
    MFelix
    8 years ago

    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

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

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's avatar
dapperscavenger
Icon for Helper V rankHelper V
8 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      dapperscavenger
      Icon for Helper V rankHelper V
      8 years ago

      Brilliant, it did the trick!  Thank you!