Forum Discussion

hackfifi's avatar
hackfifi
Helper V
6 years ago
Solved

Cumulative NULL issue

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
hackfifi
Helper V
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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