Year over Year Cost Calculation

[ejzimmerman]

Hi,

For my report, I am trying to report out on the year over year cost increase, and my source data is in a similar format to the data shown in the table below. I am trying to create a measure that will return the cost for the prior year/month/day etc. that varies based on the table or chart. I've looked around the other posts on this topic, but I can't seem to get them to work. I show a sample dataset then what the output should look like when summing by year and supplier.

Sample Dataset:

Date	Month	Year	Identifer 1	Supplier	Cost
1/1/2021	1/1/2021	2021	1	A	120
1/2/2021	1/1/2021	2021	2	A	110
1/3/2021	1/1/2021	2021	3	B	100
1/4/2021	1/1/2021	2021	4	B	90
1/5/2021	1/1/2021	2021	5	C	80
1/6/2021	1/1/2021	2021	6	C	70
1/1/2020	1/1/2020	2020	1	A	60
1/2/2020	1/1/2020	2020	2	A	50
1/3/2020	1/1/2020	2020	3	B	40

Output:

Year	Supplier	Cost	Prior Year Cost	Trend
2021	A	230	110	120
2021	B	190	40	150
2021	C	150	0	150
2020	A	110	0	110
2020	B	40	0	40
2020	C	0	0	0

[ejzimmerman]

Thank you! That solved my problem. Really appreciate the help.

[VahidDM · Accepted solution]

Hi @ejzimmerman 

Try these measures :

Prior Year = 
VAR _LY =
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        ALLEXCEPT ( 'Table', 'Table'[Supplier] ),
        PREVIOUSYEAR ( 'Table'[Date] )
    )
RETURN
    IF ( ISBLANK ( _LY ), 0, _LY )

Cost Trend = 
SUM ( 'Table'[Cost] ) - [Prior Year]

Output:

Download the sample file: https://gofile.io/d/EtW8Ls

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos 🙏!!