Forum Discussion
networking days from positive and negative duration
Hi Power BI Community
I have the following problem:
I have the custom column:
let
originalDuration = [Differenz in Tagen],
listDates = {
Int64.From( [WE_Datum] ) .. Int64.From( [Termin OTD] )
},
subtract = List.Transform(
listDates,
( i ) => let
itemDate = Date.From( i ),
listWeekends = not List.IsEmpty(
List.Select( { 6, 7 }, each _ = Date.DayOfWeek( itemDate, Day.Monday ) + 1 )
),
listHolidays = not List.IsEmpty(
List.Select( Holidays[Date], each _ = itemDate )
)
in
if listWeekends or listHolidays then true else null
),
adjustedDuration =
if originalDuration >= 0 then
originalDuration - List.NonNullCount( subtract )
else
originalDuration + List.NonNullCount( subtract )
in
Duration.From( adjustedDuration )
I am using this custom column in the query editor in Power BI (Version May 2023).
So I'm trying to get only the networking days between two dates, without the weekend.
The thing is, that it works in one way but doesn't in the other.
Here you can see the problem I face. When I have a negative difference, the weekeknd days are calculated correct. But when the difference is positive, the output is always 0. And I don't know what is wrong with the calculated column.
The expected output in the yellow highligted fields would be 2 (2 weekend days). So the difference should not be 6, it should be 4 (in the column "Differenz in AT", networkingdays). And the output in the green highligted rows have to stay the same, because they are correct.
Any help would be appreciated.
Hi Etienne_1995 ,
Please try this way. The difference in the calculated values is because we are located in different regions, so there is a time difference resulting in a day's difference, please ignore these differences.
Use this M function to create a custom column:let Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE], End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD], DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)), FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), Result = List.Count(FilteredDateList) in if [Differenz in Tagen] >= 0 then Result else (0 - Result)The final output is as below:
And here is all of the M function in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMDJR0gEyDSHMWJ1oJSN9Q2OEhCkREigmGRkMoATMG7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE = _t, OTD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WE", type date}, {"OTD", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Differenz in Tagen", each Duration.Days([WE] - [OTD])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Differenz in AT", each let Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE], End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD], DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)), FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), Result = List.Count(FilteredDateList) in if [Differenz in Tagen] >= 0 then Result else (0 - Result)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Anzahl", each [Differenz in AT] - [Differenz in Tagen]) in #"Added Custom2"
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Etienne_1995,
if you'd like to know every single step --> delete this part of code and expand the record.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDWNzIwMlHSUTIwhHNidaKVDI31DYzgcqYIuVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE_Datum = _t, #"Termin OTD" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"WE_Datum", type date}, {"Termin OTD", type date}}, "sk-SK"), Ad_AnzahlWochenendtage = Table.AddColumn(ChangedType, "Anzahl Wochenendtage", each [ d1 = List.Max({[WE_Datum], [Termin OTD]}), d2 = List.Min({[WE_Datum], [Termin OTD]}), dates = List.Dates(d2, Duration.TotalDays(d1-d2), #duration(1,0,0,0)), networkdDays = List.Select(dates, each Date.DayOfWeek(_, Day.Monday) < 5), networkDaysCount = List.Count(networkdDays) ][networkDaysCount], Int64.Type) in Ad_AnzahlWochenendtage
3 Replies
- dufoq3
Community Champion
Hi Etienne_1995,
if you'd like to know every single step --> delete this part of code and expand the record.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDWNzIwMlHSUTIwhHNidaKVDI31DYzgcqYIuVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE_Datum = _t, #"Termin OTD" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"WE_Datum", type date}, {"Termin OTD", type date}}, "sk-SK"), Ad_AnzahlWochenendtage = Table.AddColumn(ChangedType, "Anzahl Wochenendtage", each [ d1 = List.Max({[WE_Datum], [Termin OTD]}), d2 = List.Min({[WE_Datum], [Termin OTD]}), dates = List.Dates(d2, Duration.TotalDays(d1-d2), #duration(1,0,0,0)), networkdDays = List.Select(dates, each Date.DayOfWeek(_, Day.Monday) < 5), networkDaysCount = List.Count(networkdDays) ][networkDaysCount], Int64.Type) in Ad_AnzahlWochenendtage - lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi Etienne_1995 ,
Please try this way. The difference in the calculated values is because we are located in different regions, so there is a time difference resulting in a day's difference, please ignore these differences.
Use this M function to create a custom column:let Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE], End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD], DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)), FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), Result = List.Count(FilteredDateList) in if [Differenz in Tagen] >= 0 then Result else (0 - Result)The final output is as below:
And here is all of the M function in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMDJR0gEyDSHMWJ1oJSN9Q2OEhCkREigmGRkMoATMG7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WE = _t, OTD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WE", type date}, {"OTD", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Differenz in Tagen", each Duration.Days([WE] - [OTD])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Differenz in AT", each let Start = if Number.From([OTD]) < Number.From([WE]) then [OTD] else [WE], End = if Number.From([OTD]) < Number.From([WE]) then [WE] else [OTD], DateList = List.Dates(Start, Number.From(End - Start) + 1, #duration(1,0,0,0)), FilteredDateList = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5), Result = List.Count(FilteredDateList) in if [Differenz in Tagen] >= 0 then Result else (0 - Result)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Anzahl", each [Differenz in AT] - [Differenz in Tagen]) in #"Added Custom2"
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
