cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
torbenani
Frequent Visitor

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.

1 ACCEPTED SOLUTION

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
v-jayw-msft
Community Support
Community Support

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.

2.PNG3.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User IV
Super User IV

@torbenani , refer this blog can help you

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

az38
Super User II
Super User II

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
torbenani
Frequent Visitor

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

 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

torbenani
Frequent Visitor

az38 - Thanks for your help. This works perfect.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors