Calculate cumulative with missing data

[Anonymous]

Hi all,

I wanted to calculate the cummulative amount, but in week 4 there is no information so the DAX return blank.

I want to return the value of the closest week which has value. Please support.

Thank you,

Here is my DAX:

Measure =

CALCULATE(SUM(Sheet1[Value]),FILTER(ALL(Sheet1[Week]),Sheet1[Week]<=MAX(Sheet1[Week])))

Input data

Result:

[v-chenwuz-msft · Accepted solution]

Hi ThanhPham,

1 Create a new table with this

Table = VALUES('Sheet1'[week])

2 Change the last part of your measure where I have marked it.

Measure = CALCULATE(SUM(Sheet1[Value]),FILTER(ALL(Sheet1[Week]),Sheet1[Week]<=MAX(Table[week])))

3 By your pictures, I see that you use Matrix visual. Replace the week field from sheet1 table with the new week in the new table just created(As shown below).

Result :

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@Anonymous , Create a new table with a distinct week  or use generateseries

Week = distinct([Week])  // or use//  generateseries(1,53,1) 

Join this week of your table and create a measure like

calculate(sum(Table[Value]) , Filter(allselected(Week), [Week] <= max(Week[Week])))