Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @mdhopkins ,
Please illustrate your scenario with sample data and desired output.
How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
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 Number | Opened Date | Closed Date | Date | Cases Opened | Cases Closed | Rate | |
1 | 5/31/2019 | 5/31/2019 | 18 | 0 | 0% | ||
2 | 5/31/2019 | 5/30/2019 | 20 | 0 | 0% | ||
3 | 5/31/2019 | 5/29/2019 | 17 | 0 | 0% | ||
4 | 5/30/2019 | 5/28/2019 | 17 | 1 | 6% | ||
5 | 5/30/2019 | 5/27/2019 | 17 | 1 | 6% | ||
6 | 5/30/2019 | 5/26/2019 | 24 | 2 | 8% | ||
7 | 5/30/2019 | 5/25/2019 | 24 | 2 | 8% | ||
8 | 5/30/2019 | 5/24/2019 | 24 | 2 | 8% | ||
9 | 5/30/2019 | 5/23/2019 | 22 | 2 | 9% | ||
10 | 5/29/2019 | 5/22/2019 | 23 | 2 | 9% | ||
11 | 5/29/2019 | 5/21/2019 | 23 | 1 | 4% | ||
12 | 5/29/2019 | 5/20/2019 | 20 | 0 | 0% | ||
13 | 5/29/2019 | 5/19/2019 | 17 | 0 | 0% | ||
14 | 5/28/2019 | 5/18/2019 | 17 | 0 | 0% | ||
15 | 5/28/2019 | 5/17/2019 | 17 | 0 | 0% | ||
16 | 5/28/2019 | 5/16/2019 | 19 | 0 | 0% | ||
17 | 5/28/2019 | 5/15/2019 | 21 | 0 | 0% | ||
18 | 5/28/2019 | 5/14/2019 | 23 | 0 | 0% | ||
19 | 5/24/2019 | 5/13/2019 | 24 | 2 | 8% | ||
20 | 5/24/2019 | 5/12/2019 | 23 | 2 | 9% | ||
21 | 5/24/2019 | 5/11/2019 | 23 | 2 | 9% | ||
22 | 5/24/2019 | 5/10/2019 | 23 | 2 | 9% | ||
23 | 5/24/2019 | 5/9/2019 | 22 | 2 | 9% | ||
24 | 5/23/2019 | 5/8/2019 | 25 | 5 | 20% | ||
25 | 5/23/2019 | 5/7/2019 | 24 | 7 | 29% | ||
26 | 5/23/2019 | 5/6/2019 | 21 | 1 | 5% | ||
27 | 5/22/2019 | 5/5/2019 | 18 | 1 | 6% | ||
28 | 5/22/2019 | 5/4/2019 | 18 | 1 | 6% | ||
29 | 5/22/2019 | 5/3/2019 | 18 | 1 | 6% | ||
30 | 5/22/2019 | 5/22/2019 | 5/2/2019 | 14 | 1 | 7% | |
31 | 5/21/2019 | 5/1/2019 | 5 | 0 | 0% | ||
32 | 5/21/2019 | ||||||
33 | 5/21/2019 | ||||||
34 | 5/21/2019 | ||||||
35 | 5/21/2019 | ||||||
36 | 5/20/2019 | ||||||
37 | 5/20/2019 | 5/21/2019 | |||||
38 | 5/20/2019 | ||||||
39 | 5/20/2019 | ||||||
40 | 5/20/2019 | ||||||
41 | 5/20/2019 | ||||||
42 | 5/20/2019 | ||||||
43 | 5/17/2019 | 6/5/2019 | |||||
44 | 5/17/2019 | ||||||
45 | 5/17/2019 | ||||||
46 | 5/16/2019 | ||||||
47 | 5/16/2019 | ||||||
48 | 5/16/2019 | ||||||
49 | 5/16/2019 | ||||||
50 | 5/15/2019 | ||||||
51 | 5/15/2019 | ||||||
52 | 5/15/2019 | ||||||
53 | 5/15/2019 | ||||||
54 | 5/14/2019 | 5/29/2019 | |||||
55 | 5/14/2019 | 5/22/2019 | |||||
56 | 5/13/2019 | ||||||
57 | 5/13/2019 | ||||||
58 | 5/13/2019 | ||||||
59 | 5/13/2019 | ||||||
60 | 5/10/2019 | 6/5/2019 | |||||
61 | 5/10/2019 | ||||||
62 | 5/10/2019 | ||||||
63 | 5/10/2019 | ||||||
64 | 5/10/2019 | ||||||
65 | 5/9/2019 | ||||||
66 | 5/9/2019 | 6/5/2019 | |||||
67 | 5/9/2019 | ||||||
68 | 5/9/2019 | ||||||
69 | 5/9/2019 | 6/6/2019 | |||||
70 | 5/9/2019 | ||||||
71 | 5/8/2019 | 6/5/2019 | |||||
72 | 5/8/2019 | ||||||
73 | 5/8/2019 | ||||||
74 | 5/8/2019 | ||||||
75 | 5/8/2019 | ||||||
76 | 5/8/2019 | 5/15/2019 | |||||
77 | 5/7/2019 | ||||||
78 | 5/7/2019 | 5/7/2019 | |||||
79 | 5/7/2019 | 5/7/2019 | |||||
80 | 5/6/2019 | ||||||
81 | 5/6/2019 | ||||||
82 | 5/6/2019 | ||||||
83 | 5/3/2019 | ||||||
84 | 5/3/2019 | ||||||
85 | 5/3/2019 | ||||||
86 | 5/3/2019 | ||||||
87 | 5/2/2019 | 5/7/2019 | |||||
88 | 5/2/2019 | 5/13/2019 | |||||
89 | 5/2/2019 | ||||||
90 | 5/2/2019 | ||||||
91 | 5/2/2019 | 5/31/2019 | |||||
92 | 5/2/2019 | 5/7/2019 | |||||
93 | 5/2/2019 | 5/15/2019 | |||||
94 | 5/2/2019 | 5/7/2019 | |||||
95 | 5/2/2019 | ||||||
96 | 5/1/2019 | ||||||
97 | 5/1/2019 | 5/7/2019 | |||||
98 | 5/1/2019 | 5/24/2019 | |||||
99 | 5/1/2019 | 6/5/2019 | |||||
100 | 5/1/2019 | 5/2/2019 |
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |