Add rows that they are missing and adding cumulative sum

[Anonymous]

Hei all,

I have a table similar this:

However I need to buil using dax a table similar this:

I need to add the rows with data is missing and after to add the column with sum cumulative.

Is it possible to build this?

Regards,

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Try the below steps:

Step1,create date table and enter  product table:

Table2 = CALENDAR("2020,1,1","2020,1,5")

Step 2,crossjoin the two table:

Table4 = CROSSJOIN(Table2,Table3)

Step3，get the base table value:

value = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Date]=Table4[Date]&&'Table'[Product]=Table4[Product]))

Step 4,get the sum value the date<= earlier(date)

Column = CALCULATE(SUM(Table4[value]),FILTER(ALL(Table4),Table4[Product]=EARLIER(Table4[Product])&&Table4[Date]<=EARLIER(Table4[Date])))

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , I think if you use a date table joined with date of your table. Also you need to use date from date table in visual

Cumm Sales = CALCULATE(SUM(Table[Values]),filter(allselected(date),date[date] <=max(date[Date])))

If needed add +0

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.