How to calculate Cumulative Values when there are missing dates ?

[Pall]

Hi Sir,
Using line graph to display cumulative values
using below DAX. to handle missing dates and also have slicers to filter required sub_products.
**bleep** Mat Sold  =
Var _cum =CALCULATE(SUM ( Table Name [Sales] ),
'Date Table'[Date] <= MAX('Date Table'[Date]))
Return
IF(ISBLANK(SUM ( Table Name [Sales] )),_cum+0,_cum)
Example :
Date Prod Sub_Prod Sales
1/1/2022 sam sam_2 10
1/2/2022 Ram Ram_1 2
1/3/2022 sam sam_2 4
1/4/2022 Sam Sam_1 0
1/5/2022 sam sam_2 8
1/6/2022 Ram Ram_2 3
1/7/2022 Ram Ram_1 5
1/8/2022 Tem Tem_1 11
1/9/2022 Sam Sam_1 0

As you see in below table .If filter by Sub_PRod -> the graph line is going down at missing dates. Actually the graph must should have straight line at missing dates

Date Prod Sub_Prod Sales
1/1/2022 sam sam_2 10
1/3/2022 sam sam_2 4
1/4/2022 Sam Sam_1 0
1/5/2022 sam sam_2 8
1/9/2022 Sam Sam_1 0

Please help me for the solution to handle for missing dates values
Thanks in advance

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @Ashish_Mathur and @Uzi2019 , please allow me to provide another insight:

Hi  @Pall ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Sub_Prod])

2. Create measure.

Sales_Measure =
var _select=SELECTCOLUMNS('Table 2',"test",'Table 2'[Sub_Prod])
return
IF(
    MAX('Table'[Sub_Prod]) in _select,SUM('Table'[Sales]),0)

If you want to do all accumulation based on the slicer selection, you can use the following measure

Cumulative Sales =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

If you want to do year and month grouping accumulation based on the slicer selection, you can use the following measure

Cumulative Sales Group =
SUMX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

Not sure whether those 2 tables are raw data tables or not.  Just share 2 tables - input and result.  Also, specify the filter condition for generating the result table.  Share data in a format that can be pasted in an MS Excel file.

[Pall]

For suppose this is actual table with T_Sales

Date	Prod	Sub_Prod	Sales
1/1/2022	sam	sam_2	10
1/2/2022	Ram	Ram_1	2
1/3/2022	sam	sam_2	4
1/4/2022	Sam	Sam_1	0
1/5/2022	sam	sam_2	8
1/6/2022	Ram	Ram_2	3
1/7/2022	Ram	Ram_1	5
1/8/2022	Tem	Tem_1	11
1/9/2022	Sam	Sam_1	0

Need to calculate cumulative Sales 
and Result should 

Date	Prod	Sub_Prod	Sales	Cumulative Sales
1/1/2022	sam	sam_2	10	10
1/2/2022	Ram	Ram_1	2	12
1/3/2022	sam	sam_2	4	16
1/4/2022	Sam	Sam_1	0	16
1/5/2022	sam	sam_2	8	24
1/6/2022	Ram	Ram_2	3	27
1/7/2022	Ram	Ram_1	5	32
1/8/2022	Tem	Tem_1	11	43
1/9/2022	Sam	Sam_1	0	43

 
And filter data by using Slicer Sub_Prod -> Sam_1 and Sam_2 
 Result should be 

Date	Prod	Sub_Prod	Sales	Cumulative Sales
1/1/2022	sam	sam_2	10	10
1/2/2022	Ram	Ram_1	0	10
1/3/2022	sam	sam_2	4	14
1/4/2022	Sam	Sam_1	0	14
1/5/2022	sam	sam_2	8	22
1/6/2022	Ram	Ram_2	0	22
1/7/2022	Ram	Ram_1	0	22
1/8/2022	Tem	Tem_1	0	22
1/9/2022	Sam	Sam_1	0	22

If i use same "cumulative sales " line graph the line should have all the dates with +0 vlaues 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Pall]

Thanks for the quick respond. but actually in the above case also, there is missing dates with Feb. 

[Uzi2019]

Hi @Pall 
Try below dax

Cumulative = 
  VAR __Date = MAX('DIM_Date'[Date])
  VAR __Table = FILTER(ALL('Table'), [Date] <= __Date)
  VAR __Result = SUMX( __Table, [Column])
RETURN
  __Result

I hope i would resolved your issue!