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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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'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.

 

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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