Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdhopkins
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
Employee
Employee

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.