Cumulative Totals w/Multiple Dates

[jblane]

Hello Gurus!

I'm trying to produce a chart showing, the number of created and resolved tickets by month with a cumulative total of the tickets still open.

My dataset has 2 date columns to provide the dates the tickets were created and resolved. I did create a date table (autodate).

Below are the formulas I'm using to get the count of tickets created and resolved.  Any help in getting the formula for the monthly cumulative count of open (created but unresolved tickets) is appreciated.

# Created = COUNT(Report[Created])

# Resolved = CALCULATE([Resolved Count], USERELATIONSHIP(Report[RR], 'Date Table'[Date]))

# Created minus # Resolved = [# Created] - [# Resolved]   

Many Thanks,
Jason

[jblane · Accepted solution]

Thanks for the input.  Unfortunately, those options didn't help, but did get me thinking of another solution.

Here's what I did:

1) Added a Month/Year grouping to my date table.  This was key in getting the counts to roll up on a monthly basis.

2) Created a measure to capture the monthly count of open defects (Open 1 = [Defects Opened]-[Defects Closed])

3) Then used the the "New Quick Measure" feature to get the running total 

Total Open = CALCULATE([Open 1],FILTER(ALLSELECTED('Date Table'[Month/Year]),ISONORAFTER('Date Table'[Month/Year], MAX('Date Table'[Month/Year]), DESC)))

The running count total needs to carry over from one year to the next (i.e. the open count in Dec 2017 needs to carry over to Jan 2018).  The running count total for open defects works annually, but does not allow carryover. 

Any solutions for allowing a running total to span multiple years?  Solved:   Removed the Year Visual Filter

[v-yuta-msft]

Hi jblane,

You can create a measure using DAX formula below and check if it can meet your requirement:

# Created minus # Resolved =
CALCULATE (
    COUNT ( Report[Created] ) - [Resolved Count],
    ALLEXCEPT ( 'Date Table', 'Date Table'[Date] )
)

Regards,

Jimmy Tao

[Greg_Deckler]

See if my Open Tickets Quick Measure helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364