cancel
Showing results for
Did you mean:
Frequent Visitor

difference between dates in seconds excluding holiday list

hello,

Gostaria de calcular a diferença entre datas em segundos excluindo lista de feriados.

Como isso, so que em segundos:

https://community.powerbi.com/t5/Power-Query/Power-Query-Time-difference-between-two-dates-times-col...

4 REPLIES 4
Community Support

Create a new query "time_diff", then invoke it in"Table".

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

thanks for the resolution!

the power bi takes a long time to process the file, is there any way to make the query faster? I have 353k of lines and it does not process the query.

Solution Specialist

The solution is not working for you because it would never work in a real-life scenario with that complexity.

If you take a look at the function, for every row, it creates a table of TotalSeconds rows. To put this into numbers, if your startdate and enddate are just two weeks apart, it will need work over 1.209.600 rows to calculate the time difference, and if they are 4 months apart, it will work over 10.368.000 rows. And this is per row.

With your mentioned table size, and take as a minimum one day difference, you'd need to work over a minimum of 30.499.200.000 rows. If this computes, you've got a great machine there.

Anyway, anything you try will be a bit heavy, but not like that. You can take the calculations down a lot.

1) holiday_list

Let's suppose for now that your holiday_list has days and not datetimes, You should add weekends to your holidays table if needed:

``````    //GetWeekends
FirstDate = #date(2015,1,1),
LastDate = DateTime.Date(DateTime.LocalNow()),
dur = Duration.TotalDays(LastDate-FirstDate),
Cal = List.Dates(FirstDate, dur, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(Cal, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
Weekends = Table.SelectRows(Table.TransformColumnTypes(DateTable,{{"Date", type date}}), each Date.DayOfWeek([Date],Day.Monday)>=5),

AllHolidays = Table.Combine({PreviousStep, Weekends}),
Distinct = Table.Distinct(AllHolidays)
in
Distinct``````

Where PreviousStep is your previous Holiday_list step,

Note here that I use 01/01/2015 as starting day and today as ending day on the calendar. You could change that if you wish. You could also make it dynamic by using FirstDate = DateTime.Date(List.Min(Table2[startdate])), LastDate = DateTime.Date(List.Max(Table2[enddate])), where 'tb' is your data table, but then you'd need to do 3) on a separate query.

2) holiday_count

``````(st as datetime, et as datetime) =>
let
s=DateTime.Date(st),
e=DateTime.Date(et),
dates = List.Select(holiday_list[Date], each _>=s and _<=e),
datecount = List.Transform(dates, each
if _=s
then Duration.TotalDays((_ & #time(23,59,59))-st + #duration(0,0,0,1))
else
if _=e
then Duration.TotalDays(et - (_ & #time(0,0,0)))
else 1),
datesum = if s=e then 0 else List.Sum(datecount),
res = Number.Round( Duration.TotalSeconds(et - st) - 86400*datesum, 0)
in
res``````

3) Table2

``    #"Duration in Seconds" = Table.AddColumn(PreviousStep, "Duration", each holiday_count([startdate], [enddate]), Int64.Type)``

Now, supposing that your holidays are not complete days or you also have something like "it's holiday from 00:00 to 08:00 and 16:00 to 00:00", then I'd need some dummy data to be able to give you something working.

Cheers,

Spyros

Feel free to connect with me:

Community Support

I see you @ anyone else.

Best Regards

Maggie

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!