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" if: 

CreatedDate < "given date" AND

SolutionDate > "given date" OR SolutionDate is BLANK
I've tried the COUNTROWS in DAX with filters, but cant make i work. Any help is appreciated.

My objective is to ceate a chart, which week by week show the development in the number of cases.

Thanks.

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]))
)
)

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi torbenani 

    the best practice would be to create a calendar table

    Calendar Table = CALENDAR(MIN(Table[CreatedDate]), MAX(Table[SolutionDate]) )

    the add to it a measure

    Open Cases = 
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), 
Table[CreatedDate] < SELECTEDVALUE('Calendar Table'[Date]) && (Table[SolutionDate] > SELECTEDVALUE('Calendar Table'[Date]) OR ISBLANK(Table[SolutionDate]))
    • torbenani's avatar
      torbenani
      Frequent Visitor
      6 years ago

      Hi AZ38
      Thanks a lot for your help. When I use the measure, I get " The syntax for 'OR' is incorrect. "Could you gudie me.

      Open Cases =
      CALCULATE(COUNTROWS(Bi_RequestService), FILTER(ALL(Bi_RequestService),
      Bi_RequestService[createDate] < SELECTEDVALUE('Calendar'[Date]) && (Bi_RequestService[solutionDate] > SELECTEDVALUE('Calendar'[Date]) OR ISBLANK(Bi_RequestService[solutionDate]))

      Thanks

       
      • az38's avatar
        az38
        Community Champion
        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.