Dear community,
In Power Bi i'm faced with the following problem
We work in 3 shifts of 8 hours and i want to know the following
i want to count how many events are open at the start of the shift
i want to count how many events are open during the shift
i want to count how many events are open at the end of the shift
See screenshot
The shift are from 7:00 till 15:00 from 15:00 till 23:00 and from 23:00 till 7:00 the next day
The data set has a start date a time to the minute E.g. 23-11-2024 15:21
And an end date and time to the minute 24-11-2024 07:48
I have created a date table and a time table to the minute of every day.
The count of open events per dag i managed by using a inactive relationship between the date table and the fact table.
i used
But i have no idea how to get this done on shift base a metioned above.
This is basic timeslot matching (you mentioned minute level granularity so you will see 1440 a lot).
Performance is not great in DAX, could be done in Power Query as well since the data is immutable.
Caution: There may be double counting since your shift time periods overlap.
See attached.
To avoid the double counting remove the interval overlaps as I showed in my example.
A shift ends one minute before the next one starts.
For the performance issue you could choose to implement a two step validation. Once for the date range, and then only if that matches, the second one for the minute range. That way you can avoid most of the delay.
open during v2 =
VAR md =
MAX ( 'Calendar'[Date] )
VAR a =
CROSSJOIN (
FILTER ( Events, md IN CALENDAR ( [Start_date], [ClosingDate] ) ),
Shifts
)
VAR b =
ADDCOLUMNS (
a,
"matches",
IF (
COUNTROWS (
INTERSECT (
GENERATESERIES ( [StartDateTime] * 1440, [CloseDateTime] * 1440 ),
GENERATESERIES ( md * 1440 + [Start], md * 1440 + [End] )
)
) > 0,
1,
0
)
)
VAR c =
SUMX ( b, [matches] )
RETURN
IF ( c > 0, c )
This performs much better but sacrifices the date totals.
The real data has 10k+ rows
Please provide sample data that fully covers your issue.
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
