cumulative sum

[Mariam1991]

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

[Anonymous]

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?

[Mariam1991]

Thank you for your feedback , no i have 5 tables but date, sales and agency are in the same table A 

[Anonymous · Accepted solution]

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.

[Mariam1991]

sorry for being late , thank you for ur solution but how can i create a date dimension knowing that i have just a date column in table A

[Anonymous]

I've written a few times on my blog powerbi-pro.com about this, but it is in German..

Matt Masson wrote an article: https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

[Mariam1991]

thank you , i create a dimension date but the measure cumulative_sum= calculate( sum(TableA[Sales]); DATESYTD('Date'[Date]))

 But it doesn't work correcty , 

even more i use this column cumulative_sum=calculate(sum(tableA[sales]);filter(allselected('date');'date'[date]<=max('date'[date])))

i need help 😞

[Anonymous]

what is the name of your newly created date table?
Can you post a picture of it?

[Mariam1991]

Thanks very much,that's working correctly now 😉