how to get the length of time to get working hours?

[Ijimo]

how to get the length of time to get working hours?

It is necessary to take into account weekends, dinner, 7-hour working day on Friday.

My original data consist of the date and time in a two column (start - end) format: dd.mm.yyyy  HH.mm.ss

[Anonymous]

Where is your data coming from? If I was addressing this my first thought would to be to deal with it in SQL before feeding the results into PowerBI.

Working inside PowerBI do you have a time dimension available? If so you could flag all the periods of time that count as working hoursand then count up all the ones which fall between the start and end times (I've used this approach on a date table to get working days not a time table to get working hours but the principle should be similar).

[Ijimo]

Date of OData frome CRM2011.I understand that you can take the time before the transfer to the Power BI, but I wonder how you can manipulate time in PowerBI

[Greg_Deckler]

= Table.AddColumn(#"Changed Type", "Duration", each [End] - [Start])

OK, as a start, you could create a new column in your M query (see above).

When you get this into Desktop, make sure you flag it as a decimal number. Create a calculated column:

= [Duration] * 24

That will get you the total hours, let me think about and work on the second part.

[Greg_Deckler]

A pure DAX way of doing this would be:

Duration2 = DATEDIFF([Start],[End],HOUR)

[Ijimo]

What means "pure DAX way" ?

[greggyb · Accepted solution]

This one is real fun. I'll let the others help with how to change an integer of seconds into a good duration display format. I know @Greg_Deckler has helped others with that before.

This problem is all about data modelling, specifically using our knowledge of the problem space to make sure the numbers we need are available.

Time dimension with 86400 rows (one per second in the day), needs four fields at least: [Time], [WorkTimeFlagNonFriday], [WorkTimeFlagFriday], [WorkTimePriorNonFriday], [WorkTimePriorFriday], [WorkTimeAfterNonFriday], [WorkTimeAfterFriday].

Date dimension with one row per date (need contiguous dates), needs at least three fields [Date], [HoursInFullDay], [WorkDayFlag].

 There's a lot of Power Query and DAX going into this. I'll copy and paste below, but this solution would be  better explored by looking at the sample .pbix hosted on OneDrive, here.

// Power Query
// DimDate

let
    Source = List.Dates( #date(2015,1,1), Duration.Days( #date(2016,12,31) - #date(2015,1,1) ) + 1, #duration(1,0,0,0) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DayOfWeek", each Date.DayOfWeek( [Date] )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DayName", each Date.ToText( [Date], "dddd" )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "WorkDayFlag", each [DayOfWeek] <> 0 and [DayOfWeek] <> 6),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "WorkTimeInFullDay", each if [WorkDayFlag] and [DayName] <> "Friday"
then 8 * 60 * 60
else if [DayName] = "Friday"
then 7 * 60 * 60
else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"DayOfWeek", Int64.Type}, {"WorkDayFlag", type logical}, {"WorkTimeInFullDay", Int64.Type}})
in
    #"Changed Type1"

//DimTime
let
    Source = let
  NumToTime = ( num ) =>
    let
      Text = ( num ) => Number.ToText( num, "00" )
      ,Seconds = Text( Number.Mod( num, 60 ) )
      ,Minutes = Text( Number.Mod( Number.RoundDown( num / 60 ), 60 ) )
      ,Hours = Text( Number.RoundDown( num / 3600 ) )
    in
      Time.FromText( Hours & ":" & Minutes & ":" & Seconds )
in
  List.Transform( List.Numbers( 0, 86400, 1 ), NumToTime ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.ToRecord( [Time] )),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Hour", "Minute", "Second"}, {"Hour", "Minute", "Second"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "WorkTimeFlagNonFriday", each [Hour] >= 9 and [Hour] < 18 and [Hour] <> 13),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "WorkTimeFlagFriday", each [WorkTimeFlagNonFriday] and [Hour] <> 17),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "WorkTimePriorNonFriday", each let
  FullDay = 60 * 60 * 8
  ,PreLunch = 60 * 60 * 4
in
  if [WorkTimeFlagNonFriday] and [Hour] < 13
  then Duration.TotalSeconds( [Time] - #time(9,0,0) )
  else if [Hour] = 13
  then PreLunch
  else if [WorkTimeFlagNonFriday] and [Hour] > 13
  then Duration.TotalSeconds( [Time] - #time(14,0,0) ) + PreLunch
  else if [Hour] >= 18
  then FullDay
  else 0),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "WorkTimePriorFriday", each let
  FullDay = 60 * 60 * 7
  ,PreLunch = 60 * 60 * 4
in
  if [WorkTimeFlagFriday] and [Hour] < 13
  then Duration.TotalSeconds( [Time] - #time(9,0,0) )
  else if [Hour] = 13
  then PreLunch
  else if [WorkTimeFlagFriday] and [Hour] > 13
  then Duration.TotalSeconds( [Time] - #time(14,0,0) ) + PreLunch
  else if [Hour] >= 17
  then FullDay
  else 0),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "WorkTimeAfterNonFriday", each let
  FullDay = 60 * 60 * 8
  ,PostLunch = 60 * 60 * 4
in
  if [WorkTimeFlagNonFriday] and [Hour] < 13
  then Duration.TotalSeconds( #time(13,0,0) - [Time] ) + PostLunch
  else if [Hour] = 13
  then PostLunch
  else if [WorkTimeFlagNonFriday] and [Hour] > 13
  then Duration.TotalSeconds( #time(18,0,0) - [Time] )
  else if [Hour] >= 18
  then 0
  else FullDay),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "WorkTimeAfterFriday", each let
  FullDay = 60 * 60 * 7
  ,PostLunch = 60 * 60 * 3
in
  if [WorkTimeFlagNonFriday] and [Hour] < 13
  then Duration.TotalSeconds( #time(13,0,0) - [Time] ) + PostLunch
  else if [Hour] = 13
  then PostLunch
  else if [WorkTimeFlagNonFriday] and [Hour] > 13
  then Duration.TotalSeconds( #time(18,0,0) - [Time] )
  else if [Hour] >= 17
  then 0
  else FullDay),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom6",{{"Hour", Int64.Type}, {"Minute", Int64.Type}, {"Second", Int64.Type}, {"WorkTimePriorNonFriday", Int64.Type}, {"WorkTimePriorFriday", Int64.Type},  {"WorkTimeAfterNonFriday", Int64.Type}, {"WorkTimeAfterFriday", Int64.Type}, {"WorkTimeFlagNonFriday", type logical}, {"WorkTimeFlagFriday", type logical}})
in
    #"Changed Type1"


// DAX

WorkTimeBetween = 
VAR
FullTimeBetween =
// This will give us the number of seconds on the workdays between (noninclusive) the start and end dates
// This is 0 if those two dates are contiguous or the same day.
	CALCULATE(
		SUM( DimDate[WorkTimeInFullDay] )
		,DimDate[Date] > EARLIER( FactStuff[StartDate] )
		,DimDate[Date] < EARLIER( FactStuff[EndDate] )
	)
VAR
StartFriday =
// Boolean flag for whether the start date is a Friday
	LOOKUPVALUE(
		DimDate[DayName]
		,DimDate[Date]
		,FactStuff[StartDate]
	) = "Friday"
VAR
EndFriday = 
// Boolean flag for whether the end date is a Friday
	LOOKUPVALUE(
		DimDate[DayName]
		,DimDate[Date]
		,FactStuff[EndDate]
	) = "Friday"
VAR
TimeStartDay =
// Give us how much work time is left on the start date at the start time - no check for weekend starts
	IF(
		StartFriday
		,LOOKUPVALUE(
			DimTime[WorkTimeAfterFriday]
			,DimTime[Time]
			,FactStuff[StartTime]
		)
		,LOOKUPVALUE(
			DimTime[WorkTimeAfterNonFriday]
			,DimTime[Time]
			,FactStuff[StartTime]
		)
	)
VAR
TimeEndDay =
// How much work time elapsed before the end time on the end date - no check for weekend end date.
	IF(
		EndFriday
		,LOOKUPVALUE(
			DimTime[WorkTimePriorFriday]
			,DimTime[Time]
			,FactStuff[EndTime]
		)
		,LOOKUPVALUE(
			DimTime[WorkTimePriorNonFriday]
			,DimTime[Time]
			,FactStuff[EndTime]
		)
	)
RETURN
IF(
	FactStuff[StartDate] <> FactStuff[EndDate]
	// Assuming the start and end dates are separate, we have simple arithmetic with the components above
	,TimeStartDay + FullTimeBetween + TimeEndDay
	,IF(
		// For the cases where start and end happen on the same day, we can just count the valid rows in the time dimension
		// This has no check for start and end date being the same Saturday or the same Sunday. We can add a weekend check if needed.
		StartFriday
		,COUNTROWS(
			CALCULATETABLE(
				DimTime
				,DimTime[Time] >= EARLIER( FactStuff[StartTime] )
				,DimTime[Time] < EARLIER( FactStuff[EndTime] )
				,DimTime[WorkTimeFlagFriday]
			)
		)
		,COUNTROWS(
			CALCULATETABLE(
				DimTime
				,DimTime[Time] >= EARLIER( FactStuff[StartTime] )
				,DimTime[Time] < EARLIER( FactStuff[EndTime] )
				,DimTime[WorkTimeFlagNonFriday]
			)
		)
	)
)

[Ijimo]

I did it like this:

    #"Добавлен пользовательский объект" = Table.AddColumn(#"Строки с применным фильтром", "Custom", each [its_dateinwork]-[its_fillingdate]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Custom", type duration}})

[Greg_Deckler]

When you say "take into account", can you elaborate on that and provide an example of start, end times and how weekends, dinner and 7 hour working day on Friday are accounted for?

[Ijimo]

Non-working hours from 18 to 9.
Lunch from 13 to 14
Weekend: Saturday Sunday
It is necessary to subtract this time from the period