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]))
)
)
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]))
)
)
      • torbenani's avatar
        torbenani
        Frequent Visitor
        6 years ago

        az38 - Thanks for your help. This works perfect.