Running sum and 0 (zero) values

[dantheram]

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

[Anonymous · Accepted solution]

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]

Hay @dantheram 

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

[dantheram]

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

[dantheram]

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