Calculate the index of one month in year 2 relative to same month in year 1

[Anonymous]

Hi All,

I am currently working on a report, where I need help to calculate the sales index (percentage) of each month for year 2 and compare it with the same months for year 1. I have no idea how to do this. I have tried to copy the exact same table in and then make simple division calculations, but it seems to complicate matters further, as I wish to not only compare 2021 vs 2022 but also like 2022 vs 2016 etc. Can anybody help with this? Thank you in advance!

Month	2021 (units)	2022 (units)	Index
Jan	3294	2342	%
Feb	5029	201	%
Mar	12345	0	%
Apr	2398	0	%
May	3245	0	%
Jun	1456	0	%
Jul	3245	0	%
Aug	2134	0	%

[TheoC]

Hi @ 

You can create the following measure:

Movement % = 

VAR _1 = SUM ( Table[Amount] )
VAR _2 = CALCULATE ( SUM ( Table[Amount] ) , SAMPERIODLASTYEAR ( Date[Date] ) )
RETURN
IF ( AND ( _1 <> 0 , _2 <> 0 ) , DIVIDE ( _1 - _2 , _2 ) )

If you don't have a Date table, then use the Date column from your data table to replace Date[Date].  Also, make sure you convert to a % in the Format field! 

Hope this helps 🙂

Theo

[Anonymous]

Hi Theo,

Thank you for your response. I have problems getting the correct percentage calculation. I have used your calculation and inserted my own table amounts:

Movement KFP % =

VAR _1 = SUM (Orders[Number of KFP] )

VAR _2 = CALCULATE ( SUM ( Orders[Number of KFP] ) , SAMEPERIODLASTYEAR ( 'Date'[Date]) )

RETURN

IF ( AND ( _1 <> 0 , _2 <> 0 ) , DIVIDE ( _1 - _2 , _2 ))

However, I get the following output. (Disregard the DFP). Here it shows the wrong %, as Movement KFP of 2022 should be around 59% rather than 0.78%. Also, can I somehow do so movement KFP only appears at the far right and not after 2021 as well?

Thank you in advance.

Best,

Christian

[TheoC]

Hi @Anonymous 

Do you have a Date table established in your data model?

It definitely should work.  Here is a screenshot of it operational:

If you don't have a Date table, what is your Date column?

Theo

[Anonymous]

Hi Theo,

I have added a table for Dates as seen below

However, the data is supposed to track a delivery date and not the date that it was recorded. Therefore I also have this column:

[TheoC]

@Anonymous okay, sweet.

Do you have a relationship between the Date[Date] and Orders[Delivery Date] established?

[TheoC]

a minute ago

@ckolby If for some reason the relationship aspect doesn't resolve your issue, you can try the to create the following independent measures:

Sum = SUM ( Orders[Number of KFP] )

CurYr = CALCULATE ( [Sum] , DATESYTD ( 'Date'[Date] )

PriorYr = CALCULATE ( [Sum] , SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Mov YoY % = 

VAR _1 = [CurYr] - [PriorYr]
VAR _2 = _1 / [CurYr]
RETURN
IF ( _2 = 0 , 0 , _2 )

[TheoC]

@ckolby If for some reason the relationship aspect doesn't resolve your issue, you can try the to create the following independent measures:

Sum = SUM ( Orders[Number of KFP] )

CurYr = CALCULATE ( [Sum] , DATESYTD ( 'Date'[Date] )

PriorYr = CALCULATE ( [Sum] , SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Mov YoY % = 

VAR _1 = [CurYr] - [PriorYr]
VAR _2 = _1 / [CurYr]
RETURN
IF ( _2 = 0 , 0 , _2 )

[Anonymous]

Dear Theo,

Thank you very much for your response. It is highly appreciated!

I have connected the file with the date in following fashion. (and tried other cardinalities, but nothing seems to work). When I connect the two (delivery date and date), the Delivery Date seems to unformat itself, so it no longer is a date format - and I don't seem to be able to change such.

I have also tried the measures you created:

SumofKFP = sum(Orders[Number of KFP])

CurYr = CALCULATE ( [SumofKFP] , DATESYTD ( Orders[Delivery Date]))

PriorYr = CALCULATE ( [SumofKFP] , SAMEPERIODLASTYEAR ( Orders[Delivery Date] ) )

Mov YoY % =

VAR _1 = [CurYr] - [PriorYr]

VAR _2 = _1 / [CurYr]

RETURN

IF ( _2 = 0 , 0 , _2 )

This gives me such output:

Thank you in advance!

[amitchandak]

@ , You can use two slicer approach. refer my video for that

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg