Forum Discussion
Helper IIOpen 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 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!
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] ))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
7 Replies
- amitchandak
Super User
lilych , Please refer to this blog : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
you need a formula like Current employee, but tp make it start of period use Min (Date[Date]), in place of MAx(Date[Date])
- Jihwan_Kim
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] ))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
lilychThanks 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.
- ok, i'm confused!in the bellow expresressioncases 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
