Forum Discussion
Calculate cumulative with missing data
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:
Result:
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.
2 Replies
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])))
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.
