How to calculate the total hours spent for a certain period

[PBI_newuser]

Hi, how to calculate the total hours spent from Saturday after 12am to Monday before 3pm?

For example, for Job-05850449, the total hours spent from start time to end time is 2 hours but we should calculate up to Monday 3pm, so the total hours spent became 1 hour. For Job-05847239, this job should be excluded as it started after 3pm Monday.

Job	Start Datetime	End Datetime	Total Hours
Job-05847559	02/04/2022 2:30	02/04/2022 3:00		Friday
Job-05848470	02/05/2022 0:05	02/05/2022 3:10	3.08	Saturday
Job-05848542	02/06/2022 0:00	02/06/2022 2:00	2.00	Sunday
Job-05848547	02/08/2022 2:00	02/08/2022 4:00		Tuesday
Job-05848548	02/09/2022 4:00	02/09/2022 5:00		Wednesday
Job-05850384	02/10/2022 14:00	02/10/2022 15:00		Thursday
Job-05850385	02/11/2022 13:30	02/11/2022 14:00		Friday
Job-05850386	02/12/2022 15:00	02/12/2022 18:30	3.50	Saturday
Job-05850446	02/13/2022 14:00	02/13/2022 16:00	2.00	Sunday
Job-05850449	02/14/2022 13:00	02/14/2022 17:00	2.00	Monday
Job-05850450	02/15/2022 15:00	02/15/2022 16:00		Tuesday
Job-05850539	02/16/2022 15:00	02/16/2022 15:10		Wednesday
Job-05850547	02/18/2022 13:00	02/18/2022 16:00		Friday
Job-05850548	02/19/2022 12:30	02/19/2022 13:00	0.50	Saturday
Job-05847559	02/20/2022 15:30	02/20/2022 16:00	0.50	Sunday
Job-05847239	02/21/2022 15:30	02/21/2022 16:00		Monday

[Charlene-SA]

Hi all, 

I recently had this problem. 

I had to show a time duration for a call out that would be in a 24 hour period with out the date.

Some call outs did not have an end time so I had to include isblank

Here is my dax

Call Out To Client Site =

IF(ISBLANK ([EndTime]=, 0 ,If([EndTime]>=[StartTime],

[EndTime] -[StartTime],

[EndTime] + 24 -[StartTime])  

[PC2790]

Hey @PBI_newuser 

You can try this code:

Hours =
VAR StartDate =
    FORMAT ( JobTable[Start Datetime], "mm/dd/yyyy" )
VAR EndDate =
    FORMAT ( JobTable[End Datetime], "mm/dd/yyyy" )
VAR StartTime =
    FORMAT ( JobTable[Start Datetime], "hh:mm:ss" )
VAR EndTime =
    FORMAT ( JobTable[End Datetime], "hh:mm:ss" )
VAR StartHour = 00
VAR EndHour = 15
VAR StartHourValue =
    FORMAT ( StartHour / 24, "hh:mm:ss" )
VAR EndHourValue =
    FORMAT ( EndHour / 24, "hh:mm:ss" )
VAR SameDay =
    IF (
        FORMAT ( JobTable[Start Datetime], "dd/mm/yyyy" )
            = FORMAT ( JobTable[End Datetime], "dd/mm/yyyy" ),
        1,
        0
    )
VAR RequiredStartDay =
    IF (
        WEEKDAY ( JobTable[Start Datetime], 1 ) = 1
            || WEEKDAY ( JobTable[Start Datetime], 1 ) = 6
            || WEEKDAY ( JobTable[Start Datetime], 1 ) = 7,
        1,
        0
    )
VAR RequiredEndDay =
    IF (
        WEEKDAY ( JobTable[End Datetime], 1 ) = 1
            || WEEKDAY ( JobTable[End Datetime], 1 ) = 6
            || WEEKDAY ( JobTable[End Datetime], 1 ) = 7,
        1,
        0
    )
VAR NoofHours =
    //This is to calculate number of hours if the start date and end date is on the same day
    IF (
        EndTime > EndHourValue,
        DIVIDE ( DATEDIFF ( StartTime, EndHourValue, MINUTE ), 60 ),
        DIVIDE ( DATEDIFF ( StartTime, EndTime, MINUTE ), 60 )
    )
RETURN
    IF (
        //This is to calculate number of hours if the start date and end date is on the same day
        SameDay
            && RequiredStartDay,
        IF ( NoofHours < 0, BLANK (), NoofHours ),
        BLANK ()
    )

If your data contains Start Date and End Dates not same, then you will have to add a new logic for that.

[PBI_newuser]

Thank you @PC2790 ! How to change the code to capture Sat and Sun from 12am to 11.59pm? Now the code only capture Sat and Sun from 12am to 3pm but we need to capture the hours spent from Sat 12am to Mon 3pm. For example, Job-05850386's start date and end date fall on Saturday but the above code shows 0 hour cause start datetime is after 3pm.

Besides, how to edit the code if my data contains different start date and end date?

[PC2790]

In that case,try this:

Testhrs = 
VAR StartDate =
    FORMAT ( JobTbl[Start Datetime], "mm/dd/yyyy" )
VAR EndDate =
    FORMAT ( JobTbl[End Datetime], "mm/dd/yyyy" )
VAR StartTime =
    FORMAT ( JobTbl[Start Datetime], "hh:mm:ss" )
VAR EndTime =
    FORMAT ( JobTbl[End Datetime], "hh:mm:ss" )
VAR StartHour = 00
VAR EndHour = 15
VAR StartHourValue =
    FORMAT ( StartHour / 24, "hh:mm:ss" )
VAR EndHourValue =
    FORMAT ( EndHour / 24, "hh:mm:ss" )
VAR SameDay =
    IF (
        FORMAT ( JobTbl[Start Datetime], "dd/mm/yyyy" )
            = FORMAT ( JobTbl[End Datetime], "dd/mm/yyyy" ),
        1,
        0
    )
VAR RequiredStartDay =
    IF (
        WEEKDAY ( JobTbl[Start Datetime], 1 ) = 1
            || WEEKDAY ( JobTbl[Start Datetime], 1 ) = 6
            || WEEKDAY ( JobTbl[Start Datetime], 1 ) = 7,
        1,
        0
    )
VAR RequiredEndDay =
    IF (
        WEEKDAY ( JobTbl[End Datetime], 1 ) = 1
            || WEEKDAY ( JobTbl[End Datetime], 1 ) = 6
            || WEEKDAY ( JobTbl[End Datetime], 1 ) = 7,
        1,
        0
    )
VAR NoofHours =
    //This is to calculate number of hours if the start date and end date is on the same day
    IF (
        EndTime > EndHourValue,
        DIVIDE ( DATEDIFF ( StartTime, EndHourValue, MINUTE ), 60 ),
        DIVIDE ( DATEDIFF ( StartTime, EndTime, MINUTE ), 60 )
    ) //This is to check the full days between the StartDay and End Day
VAR FullDaysHours =
    (
        COUNTAX (
            FILTER (
                DateTable,
                'DateTable'[Date] > JobTbl[Start Datetime]
                    && 'DateTable'[Date] < JobTbl[End Datetime]
            ),
            'DateTable'[Date]
        )
            * DATEDIFF ( StartHourValue, EndHourValue, MINUTE )
    ) // get the full hours on the end date
VAR LastDayHours =
    IF (
        RequiredEndDay = 1,
        IF (
            EndTime > EndHourValue,
            DIVIDE ( DATEDIFF ( StartHourValue, EndHourValue, MINUTE ), 60 ),
            DIVIDE ( DATEDIFF ( StartHourValue, EndTime, MINUTE ), 60 )
        )
    )
RETURN
    IF (
        SameDay && RequiredStartDay,
        IF ( NoofHours < 0, BLANK (), NoofHours ),
        IF (
            //This is to calculate number of hours if the start date and end date is not on the same day
            SameDay = 0
                && RequiredStartDay,
            IF (
                //get the hours of the first day
                RequiredStartDay,
                IF (
                    (
                        NoofHours
                            + // get the full hours on the days between
                            DIVIDE ( FullDaysHours, 60 ) + // get the full hours on the end date
                        LastDayHours
                    ) < 0,
                    BLANK (),
                    NoofHours + DIVIDE (FullDaysHours, 60) + LastDayHours
                )
            )
        )
    )

[PBI_newuser]

Hi @PC2790 , this is the file. Below is my expected output but I couldn't get it with your suggested code. Can you help me to check if there's any error in the "Column"?

Job	Total Hours
Job-05848470	3.08
Job-05848542	2.00
Job-05850386	3.50
Job-05850446	2.00
Job-05850449	2.00
Job-05850548	0.50
Job-05847559	0.50
Total	13.58

[amitchandak]

@PBI_newuser , Try a measure like

calculate(sumx(Table, datediff([Start Datetime], [End Datetime], minutes)), filter( Table, day([Start Datetime],2) > 6 || ( Date([Start Datetime]) =1 && timevalue([Start Datetime]) < time(15,0,0))))

[PBI_newuser]

Hi @amitchandak , this measure doesn't work for Job-05850449 as it started before 3pm Monday but ended after 3pm Monday. It should calculate up to 3pm Monday. How to edit the measure to calculate up to Monday 3pm?