Forum Discussion
cumulative sum
HI everyone;
i want to calculate cumulative sum of sales but when i change the date as a filter doesn't take it in consideration like if i chose 2017 in the filter , cumulative sums doesn't calculate correctly even more if i chose other filter like agency
cumulative_sum= calculate(sum(tableA[sales]);filter(all(table A);tableA[number_month]<=Max(tableA[number_month])))
thank you in advance
the cumul
Hi Mariam1991
What you need is a date dimension. Without one, you can't achieve your goal.
Your measure is cumulating the values of the months with the same name number (1,2,....) and adding the result to the result of the next month with the same number. But it doesn't know anything about date. Month number could be anything but date, even it is derived from date in your case.
Power BI needs a date dimension to achieve year-to-date calculations.
With a date dimension, the measure is straight forward:
NEW_cumulative_sum_with_date_dimension = calculate( sum(TableA[Sales]); DATESYTD('Calendar'[Date]))your solution looks something like this:
with the measure in the code you will get this table:
Hope this helps. Please feel free to ask more questions if needed.
8 Replies
Hi Mariam1991
Do you have any relationships between the tables?
Especially from date to Sales?
Or do you have just one table for all the data?
Thank you for your feedback , no i have 5 tables but date, sales and agency are in the same table A
Hi Mariam1991
What you need is a date dimension. Without one, you can't achieve your goal.
Your measure is cumulating the values of the months with the same name number (1,2,....) and adding the result to the result of the next month with the same number. But it doesn't know anything about date. Month number could be anything but date, even it is derived from date in your case.
Power BI needs a date dimension to achieve year-to-date calculations.
With a date dimension, the measure is straight forward:
NEW_cumulative_sum_with_date_dimension = calculate( sum(TableA[Sales]); DATESYTD('Calendar'[Date]))your solution looks something like this:
with the measure in the code you will get this table:
Hope this helps. Please feel free to ask more questions if needed.
