Cumulative NULL issue

[hackfifi]

Hi All - Having this issue with Cumulative Formula. As you can see where there is NO Data for 'A' (Line 18-Oct-19), and hence i made the value =0. But the cumulative calculation is still coming up as NULL.
I need the RED BOX to show 36,929 (not NULL). Much appreciated.

A = if([Act Qty]=0,0,CALCULATE(SUMX(WBS,[Act Qty]*[UR - Base Budget])))

Cumulative A =
VAR MaxActDate = MAX(Actuals[Date])
RETURN
IF(MAX('Date'[DateOriginal])>MaxActDate,
Blank(),
CALCULATE (
[To Date Earned],
FILTER (
ALL('Date'),
'Date'[DateOriginal] <= MAX ( 'Date'[DateOriginal])
)
)
)

[v-juanli-msft · Accepted solution]

Hi @hackfifi 

I make a test as below, please try my method on your side to see if it works.

Create a measure

clc2 = IF(ISBLANK([A]),BLANK(),CALCULATE([A],FILTER(ALLSELECTED('date'),'date'[Date]<=MAX('date'[Date]))))

My other measures

A = SUMX('Table','Table'[Act Qty]*'Table'[UR - Base Budget])

clc = IF([A]<>BLANK(),CALCULATE([A],FILTER(ALLSELECTED('date'),'date'[Date]<=MAX('date'[Date]))))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hackfifi]

Thanks Everyone!! Solution has worked 🙂

[Anonymous]

Is this the only blank that is occuring? If so can you change the "Blank()" in either IF statement to the number you need? Otherwise it may be beneficial to provide us with more of the table so that we can more accurately tell you what needs to be done.

[hackfifi]

@Anonymous Thanks for your time. There are multiple blanks when the dataset is FILTERED using the selection filters. When no filter is selected, there are NO nulls in the dataset. But when a filter is selected, the measure produces some nulls/0's. Hence i put the formula of A to 0, but cumulative does not consider the value to be 0. Example of another filtered dataset below.

[Anonymous]

@hackfifi Have you tried using the FORMAT function to detect the blanks/0's?

[kentyler]

You could also try moving the ALL() outside of the FILTER() and just having it be another filter for CALCULATE

[kentyler]

is [To Date Earned] as measure. If so could we see it please.

[kentyler]

It looks like you should be using the maxdate variable you set in the beginning inside the CALCULATE statement for the <= comparison , instead of recalculating the max() there.

[hackfifi]

sorry @kentyler - [To Date Earned] is same as Formula [A]. I just renamed that table headers. Apologies for the confusion