Carrying Over Open Case Amount from Previous Month

[PBIUWO]

Hi, 

I have a data source with cases with Open and Closed status with schedule dates, and the date that it closed. 

I am trying to figure out the % of cases closed in the same month as the scheduled date. 

Ex. 10 Cases in May 2020, and 5 are closed. Thats 50% of closure rate. 

The case is scheduled, and is supposed to be closed in the same month. 

However, if it is not closed then it will be added to the total of cases to be closed until it is. (Not necessary until next month, and could be longer). 

I want to make a table like "Table A" but I think that as months change, closure rate will also change.

How should I make it so it keeps the EOM closure rate. 

Details: 

- I can run this report everyday and save it everyday 

- As soon as the case is closed, the closed date will appear

- The carryover month can be longer than just the next month

Table A:

	January	February	March	April
Previous Scheduled Cases that are still open	5	5	5	1
Scheduled Cases	5	10	4	5
Total Cases	10	15	9	6
Closed Cases	5	10	8	4
Closure Rate (%)	50%	67%	89%	67%

Sample Data: 

Case #	Status	Schedued Date	Closed Date
1	Open	February 2020
2	Closed	February 2020	March 2020
3	Closed	March 2020	April 2020
4	Open	March 2020
5	Open	April 2020
6	Closed	April 2020	April 2020
7	Open	May 2020
8	Closed	February 2020	February 2020
9	Closed	March 2020	March 2020
10	Closed	April 2020	May 2020

*I'm thinking that the carry over amount can just be calculated by counting rows with the MAX of the previous months date and as long as it's the equal or less than the schedule date with an open status. 

[v-lid-msft]

Hi @PBIUWO ,

We can create a calculated table as column and then use several measure to meet your requirement:

Calculated table:

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Month-Year",FORMAT([Date],"MMM YY"),"Sort",YEAR([Date]) * 100 + MONTH([Date]),"Year", YEAR([Date]))

Measures:

Previous Scheduled Cases that are still open = 
VAR minDate =
    MIN ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Case #] ),
        'Table'[Schedued Date] < minDate,
        'Table'[Closed Date] >= minDate || ISBLANK('Table'[Closed Date])
    )

Scheduled Cases = 
CALCULATE(DISTINCTCOUNT('Table'[Case #]),'Table'[Schedued Date] in DISTINCT('Calendar'[Date]))

Total Cases = [Previous Scheduled Cases that are still open] + [Scheduled Cases]

Closed Cases = CALCULATE(DISTINCTCOUNT('Table'[Case #]),'Table'[Closed Date] in DISTINCT('Calendar'[Date]))

Closure Rate (%) = 
DIVIDE([Closed Cases],[Total Cases],0)
// or using following without other measure
// VAR minDate =
//     MIN ( 'Calendar'[Date] )
// RETURN
//     DIVIDE (
//         CALCULATE (
//             DISTINCTCOUNT ( 'Table'[Case #] ),
//             'Table'[Closed Date] IN DISTINCT ( 'Calendar'[Date] )
//         ),
//         CALCULATE (
//             DISTINCTCOUNT ( 'Table'[Case #] ),
//              'Table'[Schedued Date] < minDate
//                , 'Table'[Closed Date] >= minDate  || ISBLANK('Table'[Closed Date])
//         )+ CALCULATE (
//             DISTINCTCOUNT ( 'Table'[Case #] ),
//             'Table'[Schedued Date] IN DISTINCT ( 'Calendar'[Date] )
//         ) ,
//         0
//     )

By the way, PBIX file as attached.

Best regards,

[PBIUWO]

@v-lid-msft 

Hi, thanks for doing a lot on this.

The CalendarAuto() helped a lot! 

[mahoneypat]

Since your data are at the monthyear granularity, you should add a Date table that has a column with Month Year in the same format as your data and make a relationship to your ScheduledDate of your Case table.  Then you can write a measure like this:

Closure Rate =

VAR currentmonthyear =

SELECTEDVALUE(Date[MonthYear])

VAR scheduledcases =
COUNT ( Case[Case#] )
VAR closedsamemonths =
CALCULATE ( COUNT ( Case[Case#] ), Case[ClosedDate] =currentmonthyear)
RETURN
closedsamemonths / scheduledcases

I couldn't tell from your post.  Did you need the other measures too?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PBIUWO]

@mahoneypat 

I have everything else, it's just this. 

Your answer is calculating only the cases that were closed in the same period.

I need to carry over the open cases across months. 

Ex. If there was a case thats open from January, and it's currently May. I need this count to be also included in February, March, April and May.  

So if there was 5 scheduled for each February, March, April and May.  , it would be 6 open cases in each of those months. Then dividing the count of cases completed in the month.  

[amitchandak]

@PBIUWO , This Article is on very similar lines

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

[PBIUWO]

amitchandak 

It does not sound like the same problem and I cannot apply this solution.

As highlighted in my question, it is not just previous months. It can be older than that.