I’m trying to create a DAX measure in Power BI that will calculate the value for the previous year based on each year present in my table. I want to create a list that shows the value for each year and its corresponding value from the previous year.
The problem is that the final result I get only shows the sum value but without the most recent year.
Previous Year Purchasing Power =
CALCULATE(
SUM(Fact_Index[Purchasing Power Index]),
SAMEPERIODLASTYEAR(Fact_Index[Date])
)
SAMPLE DATA
| Year | Purchasing Power Index | Previous Purchasing Power Index |
|---|---|---|
| 2014 | 100 | |
| 2015 | 200 | |
| 2016 | 300 | |
| SUM | 600 | 300 |
THE RESULT I WANNA GET
| Year | Purchasing Power Index | Previous Purchasing Power Index |
|---|---|---|
| 2014 | 100 | |
| 2015 | 200 | 100 |
| 2016 | 300 | 200 |
| SUM | 600 | 300 |
you need to create a date table and try below DAX
Previous Year Purchasing Power =
CALCULATE(
SUM(Fact_Index[Purchasing Power Index]),
DATEADD(DATE[date],-1,year)
)
DATEADD
2