Creating a running total using coalesced values causing running total to default to 0

[GeorgeES]

Good afternoon!

I'm having issue with creating a running total against my Date table. I have two measures:

1. Distinct count of created items

2. Distinct count of closed items

I then create a third measure which subtracts measure 2 from measure 1. This gives me the daily view of how much has been closed vs created. What I'm keen to do is turn measure 3 into a running total so I can see how much work is outstanding at the end of each day. 

The created items and closed items come from seperate tables and there are instances where there will be closed items for a date but not created items. To get round this, I have added coalesce to both measure 1 and measure 2 to turn blanks into 0 to allow the dates to show in my visuals. The issue I'm having is that the running total I am creating defaults to 0 whenever the data in created or closed is 0, instead of what I'd expect which is the value of the other (i.e. day 1 has 0 for created and 12 for closed, I'd expect to see 12, not 0). The running total is working for everything else. The measure I am using is as follows:

Running Total = VAR StartDate =

CALCULATE(MIN('Date'[Date]), ALLSELECTED('Date'[Date]))

VAR EndDate = MAX('Date'[Date]) RETURN

CALCULATE( [Closed vs open], 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate )

Sample data

I would appreciate any help anyone can give and I'm happy to provide more details if needed! 

All the best,

GeorgeES

[v-jingzhang]

Hi @GeorgeES 

You may try this measure

Running Total =
VAR StartDate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR EndDate = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( 'Date'[Date] ),
            'Date'[Date] >= StartDate
                && 'Date'[Date] <= EndDate
        ),
        [Closed vs open]
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.