Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Table1 contains data of the following nature:
Case # | Open Date | Close Date | Owner |
1a | 6/1/2021 | 6/5/2021 | Alice |
1b | 6/4/2021 | 6/22/2021 | Bob |
1c | 6/3/2021 | 6/17/2021 | Bob |
1d | 6/16/2021 | 7/1/2021 | Alice |
1e | 6/12/2021 | 7/15/2021 | Bob |
What I'm trying to do is to calculate the number of open cases for each day of the year for each owner. So I have a Calendar table that lists every day between 1/1/2021 and 12/31/2021 but I haven't figured out quite yet what kind of formula I should use for this. For example, the case 1a was open on 6/1, 6/2, 6/3 and 6/4. On 6/5 it was closed so we don't include that. Case 1b was opened on 6/4 and closed on 6/17, which means on 6/4 we had a total of 2 cases open (1a and 1b) or 1 per person. On 6/5 it goes back down to 1 because case 1a was closed and only 1b was open.
Thanks in advance!
Solved! Go to Solution.
Hi, @RefineryUnit
Please check the following methods.
Measure:
Count =
CALCULATE (
COUNT ( 'Table'[Case] ),
FILTER (
ALL ( 'Table' ),
[Owner] = SELECTEDVALUE ( 'Table'[Owner] )
&& [Open Date] <= SELECTEDVALUE ( 'Date'[Date] )
&& [Close Date] >= SELECTEDVALUE ( 'Date'[Date] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @RefineryUnit
Please check the following methods.
Measure:
Count =
CALCULATE (
COUNT ( 'Table'[Case] ),
FILTER (
ALL ( 'Table' ),
[Owner] = SELECTEDVALUE ( 'Table'[Owner] )
&& [Open Date] <= SELECTEDVALUE ( 'Date'[Date] )
&& [Close Date] >= SELECTEDVALUE ( 'Date'[Date] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |