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]),'Ca...
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.

DemoFour's avatar
DemoFour
Continued Contributor
2 years ago

Hay dantheram 

Do you have some dummy data to look at for your desired result? 

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