cancel
Showing results for
Did you mean:
Frequent Visitor

## Add multiple previous years worth of data

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
3 REPLIES 3
Super User IV

@bend , 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

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

Proud to be a Super User!

Frequent Visitor

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

Super User IV

@bend , 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])))

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors