Cumulative Total Graph - With Filters

[Anonymous]

I am reporting on case numbers, and wanted to show how many are open at any one time.  To do this I need to show a count of total cases, but only between the periods that they were opened and closed.

I have been able to create a cumulative total graph with the following combination:

Opened Case Count = COUNT('DATABASE'[Issue Type])

Closed Case Count = COUNT('Case Dates Only'[Outcome Decision Date])

OpenedSum = SUMX('Case Duration','Business Groups'[Opened Case Count])

ClosedSum = SUMX('Case Duration','Business Groups'[Closed Case Count])

PendingCount = 'Business Groups'[OpenedSum]-'Business Groups'[ClosedSum]

Then I created the following table:

Case Duration = CALENDAR(FIRSTDATE('DATABASE'[Date matter commenced]),TODAY())

And the final measure:

Cumulative Case Total = CALCULATE (SUMX('Case Duration',[PendingCount]),FILTER (ALL('Case Duration'[Date]),'Case Duration'[Date]<= MAX('Case Duration'[Date])))

My issue is, while this counts all cases, when I try to apply a filter for the Business Group - to see how many cases were open at any one time, related to that business group - the numbers go into negatives and do strange things.

I have tried applying a filter to the Opened Case Count and Closed Case count, but the result is not working.  I have also tried applying a filter in the Case Duration table, but that formula is rejected. Attempt:

Opened Case BG Count = CALCULATE(COUNT(DATABASE[Issue Type]),FILTER(ALL(DATABASE),DATABASE[Business Group] ="GroupA"))

Closed Case BG Count = CALCULATE(COUNT('Case Dates Only'[Outcome Decision Date]),FILTER(ALL(DATABASE),DATABASE[Business Group] ="GroupA"))

Is this possible?  Whether it can be integrated into the original formulas and then able to be filtered with a slicer or a completely new formula / table.

[Anonymous · Accepted solution]

Hi @Anonymous,

It seems like a common date range analysis requirement between multiple fields, you can take a look at the following link 'start date', 'end date' part if helps:

Before You Post, Read This 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

Are you able to share a sample data with sensitive data removed?

BR