Forum Discussion
Working with two date ranges
Could someone help me how to structure my data for the following data:
| Incident Id | Incident Start Date | Incident End Date |
| 1 | 1/3/2024 | 5/3/2024 |
| 2 | 1/4/2024 | 6/4/2024 |
I also have a slicer from a dim_date[Date]. It is linked to my table Incidents on Incident Start Date - Dim Date[Date]
I want to get metrics:
- number of incidents which were open before the slicer start
- number of incidents opened int he slicer period
- number of incidents closed in the slicer period.
I am not sure if the current data structure is good for that. I would like a suggestion about how I can go around it. Thank you!
Hi,
Thanks for the solution Kedar_Pande offered, and i want to offer some more information for user to refer to.
hello mazwro , you can refer to the following solution.
Sample data
And there is a calendar table, the relationship is the same as you provided.
Create the following measure
Before = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), 'Table'[Incident Start Date] <= MIN ( 'Calendar'[Date] ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Between = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), 'Table'[Incident Start Date] >= MIN ( 'Calendar'[Date] ), 'Table'[Incident End Date] <= MAX ( 'Calendar'[Date] ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Close = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), OR ( 'Table'[Incident Start Date] > MAX ( 'Calendar'[Date] ), 'Table'[Incident End Date] < MIN ( 'Calendar'[Date] ) ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Open Before Slicer =
CALCULATE(
COUNTROWS(Incidents),
'Incidents'[Incident Start Date] < MIN('dim_date'[Date]),
OR(
ISBLANK('Incidents'[Incident End Date]),
'Incidents'[Incident End Date] >= MIN('dim_date'[Date])
)
)Opened in Period =
CALCULATE(
COUNTROWS(Incidents),
'Incidents'[Incident Start Date] >= MIN('dim_date'[Date]),
'Incidents'[Incident Start Date] <= MAX('dim_date'[Date])
)Closed in Period =
CALCULATE(
COUNTROWS(Incidents),
'Incidents'[Incident End Date] >= MIN('dim_date'[Date]),
'Incidents'[Incident End Date] <= MAX('dim_date'[Date])
)If your data contains a mix of null and valid end dates:
Ensure Incident End Date has blanks for open incidents.
Link Incident Start Date to the date dimension for slicing.
If needed, create a calculated column in the date dimension to mark incidents' open status over time for advanced analysis.💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi,
Thanks for the solution Kedar_Pande offered, and i want to offer some more information for user to refer to.
hello mazwro , you can refer to the following solution.
Sample data
And there is a calendar table, the relationship is the same as you provided.
Create the following measure
Before = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), 'Table'[Incident Start Date] <= MIN ( 'Calendar'[Date] ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Between = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), 'Table'[Incident Start Date] >= MIN ( 'Calendar'[Date] ), 'Table'[Incident End Date] <= MAX ( 'Calendar'[Date] ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Close = CALCULATE ( COUNTA ( 'Table'[Incident Id] ), OR ( 'Table'[Incident Start Date] > MAX ( 'Calendar'[Date] ), 'Table'[Incident End Date] < MIN ( 'Calendar'[Date] ) ), CROSSFILTER ( 'Calendar'[Date], 'Table'[Incident Start Date], NONE ) )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
