Count employees for every 30 minute interval, based on their shift time Given as Starttime and EndTime in Datetime Format

DAX DAX Calculations
1

Hey Guys,

Here is the problem statement,

I have a Schedules table, Here is the one with Sample data

image
image709×550 17.7 KB

What I am interested in any row that has “Shift” under the Segment Column.

the Date i need is from DateOfSchedule

Based on StartTime and Stoptime , I need to count, how many employees were available during a given 30 minute time interval like below.

8
872×772 3.32 KB

In this Example, I have included the data for 14th January 2023.

So on 14th January, Employee ID - 1, he needs to be counted available for every interval begining 00:00 until 05:00 , as his start time was previous day.

but for example EmployeeID - 3 , in this case, he is counted available for every 30 minute intervals between Start Time 5:00 am and Stop Time is 2:45 pm. this is because both Start Time and End Time fall on same day.

Eventually the results need to go in a tabular format like this :

image
image171×715 4.46 KB

I have made several failed attempts to achieve this using power Query.

So sharing a clean pbx file with sample data.
schedule.pbix (109.4 KB)

Id really apprecieate any help finding the solution to this.

Either in Dax or in PowerQuery, I am curious to know whats the best approach for this

Thanks in advance.

2

Hello @jsodhi,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

No. of Employees In 30 Min. Intervals = 
VAR _vTable = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE(
                Data , 
                Data[EmployeeID] ,
                Data[DateOfSchedule] ,
                Data[StartDate] , 
                Data[StartTime] , 
                Data[StopDate] , 
                Data[StopTime] ) , 
            Data[Segment] = "SHIFT" ) ,
        "@Revised_Start_Date" , 
        IF( Data[DateOfSchedule] <> Data[StartDate] , 
            TIME( 00 , 00 , 00 ) , 
            Data[StartTime] ) , 

        "@Revised_Stop_Time" , 
        IF( Data[DateOfSchedule] <> Data[StopDate] , 
            TIME( 23 , 59 , 59 ) , 
            Data[StopTime] ) ) ,
    "@Employee_ID" , [EmployeeID] ,
    "@Date_Of_Schedule" , [DateOfSchedule] ,
    "@Revised_Start_Time" , [@Revised_Start_Date] ,
    "@Revised_Stop_Time" , [@Revised_Stop_Time] )

VAR _Cross_Joins = 
SUMMARIZE(
    FILTER(
        CROSSJOIN(
            SUMMARIZE(
                'Time' , 
                'Time'[Time] , 
                'Time'[Intervals] ) , 
            _vTable ) , 
        'Time'[Time] >= [@Revised_Start_Time] &&
        'Time'[Time] <= [@Revised_Stop_Time] ) , 
    [@Employee_ID] , 
    [@Date_Of_Schedule] , 
    [@Revised_Start_Time] , 
    [@Revised_Stop_Time] , 
    'Time'[Intervals] )

VAR _Results =
CALCULATE( DISTINCTCOUNT( Data[EmployeeID] ) , 
    _Cross_Joins )

RETURN
_Results

Final Results Of EmployyeID = 1
Final Results Of EmployyeID = 1835×612 32.1 KB

Final Results Of EmployeeID = 3
Final Results Of EmployeeID = 3823×855 51 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

No. of Employees In 30 Min Intervals - Harsh.pbix (1.1 MB)

1 Like
3

Hey Harsh, You are Aweome.

Thank you - This works perfect.

By the time i was waiting , i had figured out a way to do it with power query, but didnt quite figure out the way to do it in dax. Its great to have it in dax aswell.

It felt complicated in the begining but soon turned out to be quite simple once the approach of tacking the problem was clear.

Thank you.

4

Hello @jsodhi

Did the response from @Harsh help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

5

Can you make this available in Excel as well? I’m trying to do the same but i’m really having a hard time figuring it out. I’m a novice. thank you!