cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

How to calculate the total hours spent for a certain period

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
7 REPLIES 7
Frequent Visitor

Hi all,

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])
Community Champion

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.

Post Prodigy

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?

Community Champion

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
)
)
)
)
``````
Post Prodigy

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
Super User

@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))))

Post Prodigy

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?

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors