Forum Discussion

mdhopkins's avatar
mdhopkins
Frequent Visitor
7 years ago

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

    • mdhopkins's avatar
      mdhopkins
      Frequent Visitor

      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