How to calculate multiple year totals based on one selected year

[Anonymous]

Hi, I want to know how I can calculate sales for past 5 years based on the one year seleted from year filter

Region	Sales	Year
A	1	2020
B	2	2020
A	1	2019
B	3	2019
A	1	2018
B	4	2018
A	1	2017
B	1	2017

Expected results:

Region	2020	2019	2018	2017
A	4	3	2	1
B	10	8	5	1

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

And now you can add a measure for cumulative sales and can be shown very easily.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I have a TotalSales measure which is TotalSales = Sum(Sales[Sales])
I want to have two more measures similar to TotalSales something like

TotalSalesPrior1Year = Calculate (TotalSales,SeletedValue(Date[Year])-1)

TotalSalesPrior2Year = Calculate (TotalSales,SeletedValue(Date[Year])-2)

so when a year is selected in Year filter lets say 2020

then I should be able to display a table like this

Region	TotalSales(CurrentYearSelection)	TotalSalesPrior1Year(CurrentYearSelection-1)	TotalSalesPrior2Year(CurrentYearSelection-2)
A	4	3	2
B	10	8	5

[parry2k · Accepted solution]

@Anonymous well that is easy

TotalSalesPrior1Year = 
VAR __selectedYear = SELECTEDVALUE ( Date[Year] )
VAR __PrevYear = __selectedYear - 1
RETURN Calculate ([TotalSales],Date[Year] = __prevYear)

and do -2 for 2 years ago.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Awesome!

That works great