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.
