Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative Total Graph - With Filters

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's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous 

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

    BR

  • Anonymous's avatar
    Anonymous
    Not applicable

    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