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 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])
)
)
)
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.

9 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

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

    • hackfifi's avatar
      hackfifi
      Helper V
      6 years ago

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

    • kentyler's avatar
      kentyler
      Solution Sage
      6 years ago

      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.

  • 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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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

      Thanks Everyone!! Solution has worked 🙂