Forum Discussion

dantheram's avatar
dantheram
Helper II
2 years ago
Solved

Running sum and 0 (zero) values

hi all

 

could you help me fix this forumla?

 

CALCULATE (
    SUM ('Incident Facts'[PfPI Minutes]),
    FILTER (ALLSELECTED('Calendar'[Date],'Calendar'[Period],'Calendar'[Financial Year]),'Calendar'[Date] <= MAX ('Incident Facts'[Incident Create Date] )))
 
what happens is when a date has a 0 value the report returns a blank, rather than using the value for the preceeding date. 
 
e.g. 
A - 1
B - 2
C - 0
D - 4
 
should be -
A - 1
B - 3
C - 3
D - 7
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dantheram ,

    For the example you mentioned I think it can be solved using the overlay method. I create a table as you mentioned.

    Then I create a new measure to write the DAX code.

    Measure =
VAR _currentKey =
    SELECTEDVALUE ( 'Table'[Key] )
VAR _previousKey =
    CALCULATE (
        MAX ( 'Table'[Key] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Key] <= _currentKey )
    )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Key] <= _previousKey ),
        'Table'[Value]
    )

    Finally we will get what we want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • dantheram's avatar
      dantheram
      Helper II
      2 years ago

      nearly fix it with this but it doesnt show "0" at the very start. so if week 1 is blank it leaves it blank rather than "0". as soon as a values occurs for a week it works perfectly 

       

      Run2 = Var _lastDate =
          CALCULATE(max('Incident Facts'[Incident Create Date]),REMOVEFILTERS())
          RETURN
          CALCULATE(
              SUM('Incident Facts'[PfPI Minutes]),
              USERELATIONSHIP ( 'Calendar'[Date], 'Incident Facts'[Incident Create Date] ),
              FILTER(
                  ALLSELECTED('Calendar'),
                  'Calendar'[Date] <= MAX ('Calendar'[Date])
                  && 'Calendar'[Financial Year] = MAX( 'Calendar'[Financial Year])
                  && MIN('Calendar'[Date] ) <= _lastDate))
    • dantheram's avatar
      dantheram
      Helper II
      2 years ago

      i'll create some dummy data - i have half fixed it but it caused another graph to miscalculate now 😞

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dantheram ,

    For the example you mentioned I think it can be solved using the overlay method. I create a table as you mentioned.

    Then I create a new measure to write the DAX code.

    Measure =
VAR _currentKey =
    SELECTEDVALUE ( 'Table'[Key] )
VAR _previousKey =
    CALCULATE (
        MAX ( 'Table'[Key] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Key] <= _currentKey )
    )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Key] <= _previousKey ),
        'Table'[Value]
    )

    Finally we will get what we want.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.