Open and closed cases over time

[lilych]

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 Id	Created On	Closed On
1	11/20/2019
2	11/16/2020
3	2/1/2021	2/9/2021
4	2/5/2021	4/8/2021

REPORT:

	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21
Cases Open At Start of Time Period	1	1	1	1	1	1	1	2	2	2	3	3
Cases Open During Time Period	0	0	0	0	0	0	1	0	0	2	0	0
Cases Closed During Time Period	0	0	0	0	0	0	0	0	0	1	0	1

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

[Jihwan_Kim · Accepted solution]

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.

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

)

https://www.dropbox.com/s/4wx637ujxca1mch/lilych.pbix?dl=0 

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

[RUSSGOVE]

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

[lilych]

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?

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 -

[Jihwan_Kim]

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.

[lilych]

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

[Jihwan_Kim]

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

[amitchandak]

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