Forum Discussion

torbenani's avatar
torbenani
Frequent Visitor
6 years ago
Solved

Count open support case

For a service desk application, I want to count the number of open cases a any given time. I have tow date columns "CreatedDate" and "SolutionDate". I case is considered to be open at any "given date...
Need Help
  • az38's avatar
    az38
    6 years ago

    torbenani 

    Oh, sorry, my bad

    Open Cases = 
CALCULATE(COUNTROWS(Table), 
FILTER(ALL(Table), 
Table[CreatedDate] < SELECTEDVALUE('Calendar Table'[Date]) && 
(Table[SolutionDate] > SELECTEDVALUE('Calendar Table'[Date]) || ISBLANK(Table[SolutionDate]))
)
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi torbenani ,

 

Please check following steps as below.

1# Create calculated table.

CALENDAR = CALENDAR(MIN('Table'[Created_on]),TODAY())

2# Create measures.

isopen =
IF (
    SELECTEDVALUE ( 'Table'[Created_on] ) <= SELECTEDVALUE ( 'CALENDAR'[Date] )
        && (
            SELECTEDVALUE ( 'Table'[Closed_on] ) >= SELECTEDVALUE ( 'CALENDAR'[Date] )
                || ISBLANK ( SELECTEDVALUE ( 'Table'[Closed_on] ) )
        ),
    1,
    0
)

counts = SUMX('Table',[isopen])

 3# Use 'CALENDAR'[Date] as slicer.

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.