Forum Discussion

cdfizz17's avatar
cdfizz17
Frequent Visitor
3 years ago

DAX Measure - Graph Active Cases Over Time

Hello,

 

I'm writing a measure to calculate the total "active" help desk cases at any point in time.

Cases are "active" between their [EntryDate] and [CloseDate] inclusive.

 

I want to graph this data over time in a line chart, and I want to adjust the time window using a date slicer.

I have a 'Date' table and a 'Cases' table, with [Date] 1:* [EntryDate].

 

Cases with an [EntryDate] outside the date window should still be counted if they are active inside the date window. I've accomplished this by using ALL('Cases') in my DAX (see below).

 

The problem is, I also want to filter the measure by [Dept]. I don't see how I can do this while using ALL('Cases'). How can I solve this problem?

 

CaseVolume =
VAR vol =
    CALCULATE (
        COUNT ( 'Cases'[CaseID] ),
        FILTER (
            ALL ( 'Cases' ),
            'Cases'[EntryDate] <= MAX ( 'Date'[Date] )
                && (
                    'Cases'[CloseDate] >= MAX ( 'Date'[Date] )
                        || ISBLANK ( 'Cases'[CloseDate] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( vol ), 0, vol )

 

Case Table:

 

2 Replies

    • cdfizz17's avatar
      cdfizz17
      Frequent Visitor
      3 years ago

      Thank you for the answer. Unfortunately, there is still an issue.

       

      On 17th May, there were 45 active cases. Using ALLSELECTED, only 19 are counted. There are 26 missing from the total. They are filtered out by the date slicer, because the [EntryDate] is before 17th May...