How to calculate Cumulative Totals when there are missing dates ?

[Anonymous]

Hello! I am fairly new to PowerBI development and DAX queries in general and I am finding it incredibly hard to understand the language and get help in general. Here is my problem : 

I have sales data at a month level in Table 1 and a standard date table called TimeDim. The blank values are due to missing information in Table 1 for the corresponding months. Pretty standard. Both the tables are joined on Date. 

Month	Nominations
Jan, 2018
Feb, 2018
Mar, 2018	2
Apr, 2018
May, 2018
Jun, 2018	2
Jul, 2018	4
Aug, 2018	4
Sep, 2018	1
Oct, 2018	1
Nov, 2018	2
Dec, 2018	15
Jan, 2019	10
Feb, 2019	2
Mar, 2019	3
Apr, 2019	7
May, 2019	6
Jun, 2019
Jul, 2019	3
Aug, 2019	44
Sep, 2019	43
Oct, 2019	233
Nov, 2019	655
Dec, 2019	2022

As seen everywhere, the standard DAX code for cumulative totals is 

cumu_total = calculate(sum(table1'[Nominations]), filter(timedim, timedim'[date] <= max(timedim'[date]))) which should provide me the cumulative totals at a month level. 

I am using timedim in the filter expression since I already joined them. Now here are the values I get which I am unable to explain or understand. I tried sumx(), used table 1 instead of timedim, used +0, and none of them work. 

The following months are erroneous: 

Apr 2019 

Aug 2019

Sep 2019 and so on 

Month	#Nominations	Cumulative Nominations
Jan, 2018
Feb, 2018
Mar, 2018	2	2
Apr, 2018		2
May, 2018		2
Jun, 2018	2	4
Jul, 2018	4	8
Aug, 2018	4	12
Sep, 2018	1	13
Oct, 2018	1	14
Nov, 2018	2	16
Dec, 2018	15	31
Jan, 2019	10	41
Feb, 2019	2	43
Mar, 2019	3	46
Apr, 2019	7	51
May, 2019	6	55
Jun, 2019		55
Jul, 2019	3	58
Aug, 2019	44	101
Sep, 2019	43	140
Oct, 2019	233	363
Nov, 2019	655	990
Dec, 2019	2022	2874

Please provide me with some direction on how to address this as DAX solutions, are extremely hard to find and understand.

Thank you very much. I hope I have provided the context completely. 

[amitchandak]

You should use a calendar that has continuous dates.

Try like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

[aw0979]

I have been searching for 3 days for the answer to my problem. You are amazing!! Thank you so much for sharing your pbix file so I can actually follow what's going on!!!!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Pall]

Hi Sir,

do we have the above PBI file in this case. it seems same problem.

[Ashish_Mathur]

I do not have that file now.

[Pall]

Could you please provide Dax logic if you have.