cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lilych
Helper II
Helper II

Open and closed cases over time

Hello,

 

I'm trying to create a report that shows the number of cases:

  • Open that month (new and cases that remained open from preceding months)
  • Opened during month
  • Closed during month

 

SAMPLE DATA:

Case IdCreated OnClosed On
111/20/2019 
211/16/2020 
32/1/20212/9/2021
42/5/20214/8/2021

 

REPORT:

 May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20Jan-21Feb-21Mar-21Apr-21
Cases Open At Start of Time Period111111122233
Cases Open During Time Period000000100200
Cases Closed During Time Period000000000101

 

How would I go about doing something like this in Power BI? Thanks in advance!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @lilych 

Please check the below picture and the sample pbix file's link down below.

I suggest having an inactive relationship like below.

All measures are in the sample pbix file.

 

Picture1.png

 

cases open at start of time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
FILTER (
'cases',
'cases'[Created On] <= MAX ( dates[date] )
&& OR ( 'cases'[Closed On] >= MIN ( dates[date] ), 'cases'[Closed On] = BLANK () )
)
)
 
cases open during time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
USERELATIONSHIP ( dates[date], 'cases'[Created On] )
)
 
cases closed during time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
NOT ISBLANK ( 'cases'[Closed On] ),
USERELATIONSHIP ( dates[date], 'cases'[Closed On] )
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

Hi, @lilych 

Please check the below picture and the sample pbix file's link down below.

I suggest having an inactive relationship like below.

All measures are in the sample pbix file.

 

Picture1.png

 

cases open at start of time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
FILTER (
'cases',
'cases'[Created On] <= MAX ( dates[date] )
&& OR ( 'cases'[Closed On] >= MIN ( dates[date] ), 'cases'[Closed On] = BLANK () )
)
)
 
cases open during time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
USERELATIONSHIP ( dates[date], 'cases'[Created On] )
)
 
cases closed during time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
NOT ISBLANK ( 'cases'[Closed On] ),
USERELATIONSHIP ( dates[date], 'cases'[Closed On] )
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

ok, i'm confused! 
in the bellow expresression
 
cases open at start of time periods =
CALCULATE (
COUNTROWS ( 'cases' ),
FILTER (
'cases',
'cases'[Created On] <= MAX ( dates[date] )
&& OR ( 'cases'[Closed On] >= MIN ( dates[date] ), 'cases'[Closed On] = BLANK () )
)
 
shouldn't 
'cases'[Created On] <= MAX ( dates[date] )
be 
'cases'[Created On] <= MIN ( dates[date] )
I mean if it was open at the start of the period it must have men opened some tome before the period began
 
 

Thanks @Jihwan_Kim - this working great so far.

 

I am running into an issue with the cases closed during the time period where it is not allocating it correctly (see snapshot below). I double checked the relationship on the date table to the case table and all looks correct. It is inactive. Do you know why this may be happening?

 

lilych_0-1622050130139.png

 

 

Also, my date table has a wide range of dates (1900-2100). I think this may be affecting the range of dates in the matrix. Is there a way I can limit the dates shown so that it is only showing up to the current month?

 

Thank you -

Hi, @lilych 

Thank you for your feedback.

I am not sure, but please check your measure contains the below condition.

 

NOT ISBLANK ( 'cases'[Closed On] ),

 

Or, please share your sample pbix file's link here, then I can try to look into it to find out the cause.

Thanks.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Thank you - here is my test pbix file https://www.dropbox.com/s/u7z245g7hdzrx0f/Test%20Cases.pbix?dl=0

 

Hi, @lilych 

Please check the below.

 

https://www.dropbox.com/s/igbo06o9oh7xc5z/Test%20Cases.pbix?dl=0 

 

The reason is that the column was not date-type. It was dateandtime-type, and this cannot be connected to dim-date table. I added time-column, and changed the initial column's data type to date type.

 

Thanks.

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User
Super User

@lilych , Please refer to this blog : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

you need a formula like Current employee, but tp make it start of period use Min (Date[Date]), in place of MAx(Date[Date])

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.