I have aggregated the monthly sales from my salesorder table to a table containing the monthly sales information. What I'm trying to do is create a column that references the monthly sales from the previous year to show the monthly change.
This is a model of what I have so far:
| Month | Year | ThisMonthSales |
| January | 2018 | $1000 |
| January | 2019 | $2000 |
| January | 2020 | $3000 |
| January | 2021 | $4000 |
| February | 2019 | $1500 |
| February | 2020 | $2500 |
This is what I'd like to create:
| Month | Year | ThisMonthSales | LastYearMonthSales |
| January | 2018 | $1000 | |
| January | 2019 | $2000 | $1000 |
| January | 2020 | $3000 | $2000 |
| January | 2021 | $4000 | $3000 |
| February | 2019 | $1500 | |
| February | 2020 | $2500 | $1500 |
I tried this command, but the issue I'm running into is that the calculated column contains the yearly aggregated sales instead of the monthly sales:
Column =
VAR Last_Year = 'Table'[Year] - 1
RETURN
CALCULATE(SUM('Table'[This Month Sales]), ALL('Table'), 'Table'[Year] = Last_Year, 'Table'[Month] = 'Table'[Month])
What's the best way for me to accomplish this?
