cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bend
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:

Product2020202120222023
Prod110203040
Prod220406080
Prod31357

 

 

Then, a running total output would look like this:

Product2020202120222023
Prod1103060100
Prod22060120200
Prod314916
3 REPLIES 3
amitchandak
Super User IV
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
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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

bend
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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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