Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I’m trying to figure out how to be able to calculate open services and be able to show a daily history. I can’t seem to figure out the DAX. I’m guessing it needs to be a calculated field saying something like Open Service = Count of Services where Initiation date <= Calendar date and Repair date > Calendar date. Do I link the date fields to Calendar? I’m lost. Thank you in advance!
Service # | Initiation Date | Repair Date |
1 | 1/1/2022 | 1/6/2022 |
2 | 1/2/2022 | 1/3/2022 |
3 | 1/5/2022 | 1/12/2022 |
4 | 1/10/2022 | 1/15/2022 |
5 | 1/11/2022 | 1/11/2022 |
Date | Open Services | Description |
1/1/2022 | 1 | Service #1 is open |
1/2/2022 | 2 | Service #1 and #2 are open. |
1/3/2022 | 1 | Service #1 is open, #2 was completed |
1/4/2022 | 1 | Service #2 is complete. Service #1 is still open. |
1/5/2022 | 2 | Service #1 and 3 are open. |
1/6/2022 | 1 | Service #3 is open. Service #1 is completed. |
1/7/2022 | 1 | Service #3 is open. |
1/8/2022 | 1 | Service #3 is open. |
1/9/2022 | 1 | Service #3 is open. |
1/10/2022 | 2 | Service #3 and #4 are open. |
1/11/2022 | 2 | Service #3 and #4 are open. Service #5 open and completed same day. |
1/12/2022 | 1 | Service # 4 is open. Service #3 is completed |
1/13/2022 | 1 | Service #4 is open. |
1/14/2022 | 1 | Service #4 is open. |
1/15/2022 | 0 | Service #4 is completed. |
Solved! Go to Solution.
You don't need to link the dates to your calendar table. You can define a measure like
Num open tickets =
var minDate = MIN('Calendar'[Date])
var maxDate = MAX('Calendar'[Date])
return CALCULATE( COUNTROWS(Tickets'), Tickets[Initiation date] <= minDate &&
( Tickets[Repair date] >= maxDate || ISBLANK(Tickets[Repair date]) )
)
You don't need to link the dates to your calendar table. You can define a measure like
Num open tickets =
var minDate = MIN('Calendar'[Date])
var maxDate = MAX('Calendar'[Date])
return CALCULATE( COUNTROWS(Tickets'), Tickets[Initiation date] <= minDate &&
( Tickets[Repair date] >= maxDate || ISBLANK(Tickets[Repair date]) )
)