Forum Discussion
Cumulative sales with multiple years
Hi, the line graph above is exactly what I need. This is the code for the graph:
Cumulative = CALCULATE(SUM('INS_Booking'[USDN3]),FILTER(ALLSELECTED('INS_Booking'),'INS_Booking'[date_]<=MAX('INS_Booking'[date_])),VALUES('INS_Booking'[Year of Sale]))
My problem is that this needs "year" in the legend on the line graph to create, otherwise it continues the cumulation and looks like this:
The first one works but I would prefer to create each year as it's own measure so that I can put them on tables/matrix. When I try to do them seperately I get this:
Here's the code for one of the years, its the same for both years but I just switched the measure for total sales in that year
2019 Cumulative = CALCULATE([Book2019],FILTER(ALLSELECTED('Rolling_Calendar'),'Rolling_Calendar'[Date]<=MAX('Rolling_Calendar'[Date])))Note: Book2019 and Book2018 just filter [USDN3], my revenue column I used in the last code.
It looks like the 2018 cumulative wants to keep going and returns its 2018 total for each week that 2019 is entered.
Again, the goal for me is to make the first graph but have all the year cumulations as seperate measures.
Thank you to anyone who gives this a shot!
3 Replies
- v-frfei-msft
Community Support
Hi Anonymous ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
Frank
- AnonymousNot applicable
Hi Frank,
Unfortunately, we're pretty strict about sending stuff and it's mostly confidential. I've been told to hold off. Do you think theres a way we can take a swing at it without the dataset?
Basically I just need cumulation measures that can be put on the same graph/table and can be compared by week number. So that I can compare 2017 cumulation with 2018 and 2019.
The problem is that for the measure wants to look at MAX date (today) and get the cumulative total rather than the max date in the year specified in the measure. IDK if any of this makes sense haha here's a graph with 2017 nuimbers too.
It's just repeating the total because it's looking at the max date (today) and for 2017 it returns it's cumulation but I want it to return the cumulation as if I were inputting same period last year.2017 Cumulative = CALCULATE([Book2017],FILTER(ALLSELECTED('INS_Booking'),'INS_Booking'[date_]<=(MAX('INS_Booking'[date_]))))- v-frfei-msft
Community Support
Hi Anonymous ,
Please try to use this formula.
2017 Cumulative = var maxdate = CALCULATE(MAX('INS_Booking'[date_]),ALLSELECTED('INS_Booking')) return result= CALCULATE([Book2017],FILTER('INS_Booking','INS_Booking'[date_]<=maxdate))Regards,
Frank