Add multiple previous years worth of data

[Anonymous]

I have a measure that forecasts out yearly sales, from 2021 - 2023. Each sale will have reoccurring fees associated with it.

In 2021, we will only have fees associated with sales in 2021. 

In 2022, we have fees associated with sales in 2022 + all the items sold in 2021.

In 2023, we have all fees associated with sales in 2023 + 2022 + 2021. 

I need a measure that shows the total reoccuring fees expected for each year.

This needs to be scalable, so hardcoding a measure '2021 Sales Total', '2022 Sales Total', etc will not work if I want to add more years.

For example, sales matrix could look like this:

Product	2020	2021	2022	2023
Prod1	10	20	30	40
Prod2	20	40	60	80
Prod3	1	3	5	7

Then, a running total output would look like this:

Product	2020	2021	2022	2023
Prod1	10	30	60	100
Prod2	20	60	120	200
Prod3	1	4	9	16

[amitchandak]

@Anonymous , You can create this year and prior year measure with or without time intelligence

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks @amitchandak . I will look into the links you provided.

I have edited my post to include sample tables to outline what I am talking about

[amitchandak]

@Anonymous , In case you have date cumulative should work like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

In the case of only year with the year/date table

Cumm Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]<=max('Date'[Year])))