Forum Discussion
Year over Year Cost Calculation
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 |
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 🙏!!
2 Replies
Thank you! That solved my problem. Really appreciate the help.
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 🙏!!
