cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support

Hi @giovannafnr88 

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

Capture1.JPGCapture2.JPGCapture3.JPG

 

download my file

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EVpQT7LJ_2FCrNATqus...

 

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.

@cstjohn 

 

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.

 

 

Hi @giovannafnr88,

 

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

    //AddWeekends
    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:
LinkedIn

Hi @giovannafnr88 

Is this reply to me?

Is my answer helpful?

I see you @ anyone else.

 

Best Regards

Maggie

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors