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

Running Case Closure Rate

I am trying to create a measure that calculate a rolling closure rate for cases. I have a table with case data including the date that the case was opened and the date that the case was closed (if it is closed). I am trying to calculate the rolling rate for a period determined by an embedded parameter.

 

I created a date fact table and linked it to the date opened field in my case data. Using the following formula, I am able to count the number of cases opened in the period:

 

Opened in Period = CALCULATE(
    DISTINCTCOUNT('Master Case Data'[Case Number]), DATESINPERIOD('OpenDate'[Date], LASTDATE('OpenDate'[Date]), -'Period Duration (in days)'[Rolling Period (in days) Value], DAY)
)

where 'Period Duration (in Days])'[Rolling Period (in days) Value] is the parameter. This works perfectly and is the denominator for my closure rate calculation. Where I am having the issue is counting the number of cases that were closed (numerator). I started by repeating the steps (date table linked to closed date). This counts the number of cases that were closed in the period:

 

Closed in Period = CALCULATE(
    DISTINCTCOUNT('Master Case Data'[Case Number]), DATESINPERIOD('CloseDate'[Date], LASTDATE('CloseDate'[Date]), -'Period Duration (in days)'[Rolling Period (in days) Value], DAY)
)

However, I only want to count the cases if they were opened in the same period. In essence, I need to filter the 'Master Case Data' table by the open date:

TODAY() - 'Period Duration (in days)'[Rolling Period (in days) Value] <= open date

I have been unable to figure out how to do this though. I cannot seem to filter the 'Master Case Data' table before I  do my DISTINCTCOUNT() in my 'Closed in Period' measure.

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Running Case Closure Rate

Hi @mdhopkins ,

 

Please illustrate your scenario with sample data and desired output.

How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mdhopkins Frequent Visitor
Frequent Visitor

Re: Running Case Closure Rate

Here is a sample data set with desired/expected output (with the parameter for period length set to 7):

 

Sample Data Set   Desired Output  
Case NumberOpened DateClosed Date DateCases OpenedCases ClosedRate
15/31/2019  5/31/20191800%
25/31/2019  5/30/20192000%
35/31/2019  5/29/20191700%
45/30/2019  5/28/20191716%
55/30/2019  5/27/20191716%
65/30/2019  5/26/20192428%
75/30/2019  5/25/20192428%
85/30/2019  5/24/20192428%
95/30/2019  5/23/20192229%
105/29/2019  5/22/20192329%
115/29/2019  5/21/20192314%
125/29/2019  5/20/20192000%
135/29/2019  5/19/20191700%
145/28/2019  5/18/20191700%
155/28/2019  5/17/20191700%
165/28/2019  5/16/20191900%
175/28/2019  5/15/20192100%
185/28/2019  5/14/20192300%
195/24/2019  5/13/20192428%
205/24/2019  5/12/20192329%
215/24/2019  5/11/20192329%
225/24/2019  5/10/20192329%
235/24/2019  5/9/20192229%
245/23/2019  5/8/201925520%
255/23/2019  5/7/201924729%
265/23/2019  5/6/20192115%
275/22/2019  5/5/20191816%
285/22/2019  5/4/20191816%
295/22/2019  5/3/20191816%
305/22/20195/22/2019 5/2/20191417%
315/21/2019  5/1/2019500%
325/21/2019      
335/21/2019      
345/21/2019      
355/21/2019      
365/20/2019      
375/20/20195/21/2019     
385/20/2019      
395/20/2019      
405/20/2019      
415/20/2019      
425/20/2019      
435/17/20196/5/2019     
445/17/2019      
455/17/2019      
465/16/2019      
475/16/2019      
485/16/2019      
495/16/2019      
505/15/2019      
515/15/2019      
525/15/2019      
535/15/2019      
545/14/20195/29/2019     
555/14/20195/22/2019     
565/13/2019      
575/13/2019      
585/13/2019      
595/13/2019      
605/10/20196/5/2019     
615/10/2019      
625/10/2019      
635/10/2019      
645/10/2019      
655/9/2019      
665/9/20196/5/2019     
675/9/2019      
685/9/2019      
695/9/20196/6/2019     
705/9/2019      
715/8/20196/5/2019     
725/8/2019      
735/8/2019      
745/8/2019      
755/8/2019      
765/8/20195/15/2019     
775/7/2019      
785/7/20195/7/2019     
795/7/20195/7/2019     
805/6/2019      
815/6/2019      
825/6/2019      
835/3/2019      
845/3/2019      
855/3/2019      
865/3/2019      
875/2/20195/7/2019     
885/2/20195/13/2019     
895/2/2019      
905/2/2019      
915/2/20195/31/2019     
925/2/20195/7/2019     
935/2/20195/15/2019     
945/2/20195/7/2019     
955/2/2019      
965/1/2019      
975/1/20195/7/2019     
985/1/20195/24/2019     
995/1/20196/5/2019     
1005/1/20195/2/2019     

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 368 members 3,716 guests
Please welcome our newest community members: