Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone
I am trying to count the number of cases with matching person-id's and several other conditions.
I have one table consisting of all the cases. Each person could have had several cases over time.
My mission is to calculate the total case length of each person - but only if the interval between the recent start date and the last end date is less than 1 year.
Example below:
The new column should reflect the total case length of each active case. There is less than a year between the start date of person 1's recent case and the last end date of the latest case. Therefore the total case length of this case is DATEDIFF(10/11-2020, TODAY).
In the case of person 2 on the other hand, there is more than a year between the recent start date and the latest end date. Therefore the total
I hope you guys can help me with this one.
Case id | Person id | Start date | End date | Status | Total case length (new) |
1 | 1 | 10/11-2020 | 04/02-2021 | Closed | DATEDIFF(10/11-2020, 04/02-2021) |
2 | 1 | 05/05-2021 | Active | DATEDIFF(10/11-2020, TODAY()) | |
3 | 2 | 03/02-2019 | 05/10-2019 | Closed | DATEDIFF(03/02-2019, 05/10-2019) |
4 | 2 | 01/01-2021 | Active | DATEDIFF(01/01-2021, TODAY()) |
Solved! Go to Solution.
Hi,
Here is one way to do this (I assume here you want do display the result in a visual).
Start data:
Calculated column:
End result (Here I placed the filter measure to filters):
I hope this helps to solve your issue and if it does consider accepting this as a solution!
Proud to be a Super User!
@MarcLykke Hi!
You can calculate a column like this:
Hi,
Here is one way to do this (I assume here you want do display the result in a visual).
Start data:
Calculated column:
End result (Here I placed the filter measure to filters):
I hope this helps to solve your issue and if it does consider accepting this as a solution!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |