calculate time

[Anonymous]

Hi,

I am trying to calculate the time between two timestamps witihout weekends and holidays but dont know how. Also the time should only calculate working hours from 08:00 - 17:00. The time that has to be calculated is the begin and end time for incoming tickets for our service desk.

I have marked the columns of which the calculations has to be done:

[BjoernSchaefer]

Hi @Anonymous ,

i've dealed with a similar task in the past and realised it with a Date-Table created in PowerQuery wich contains a Row that defines a Workday. I also used a Webquery from a website where i can get the national holidays from. To calculate the Net-WorkingDays and Hours i did a step by step PowerQuery wich is working with a function that creates a list of dates between two dates. It's hard to describe but here are the Queries:

Function that creates the list of Dates between 2 Dates:

let
    Func = (startDate as date, numberOfMonthlyRates as number) => 
        List.Generate( 
            () => [currentDate = startDate , currentMonth = 0],
            (_) => _[currentMonth] < numberOfMonthlyRates,
            (_) => [currentMonth =  [currentMonth] + 1, currentDate = Date.AddDays([currentDate],1)],
            (_) => _)
in
    Func

CalendarTabel with Dimensions and Column containing the WorkDays:

let
    Source = List.Dates,
    InvokedSource = Source(#date(2017,1,1),Duration.Days(DateTime.Date(Date.EndOfYear(DateTime.FixedLocalNow()))-#date(2017,1,1)),#duration(1,0,0,0)),
    #"In Tabelle konvertiert" = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Datum"}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Datum", type date}}),
    #"Jahr eingefügt" = Table.AddColumn(#"Geänderter Typ", "Year", each Date.Year([Datum]), Int64.Type),
    #"Monat eingefügt" = Table.AddColumn(#"Jahr eingefügt", "MonthID", each Date.Month([Datum]), Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Monat eingefügt", "Month", each Date.ToText([Datum],"MMM")),
    #"Quartal eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "QuartalID", each Date.QuarterOfYear([Datum]), Int64.Type),
    #"Wochentag eingefügt" = Table.AddColumn(#"Quartal eingefügt", "WeekdayID", each Date.DayOfWeek([Datum])+1, Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Wochentag eingefügt", "Weekday", each Date.ToText([Datum],"ddd")),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "KWID Teil1", each Number.From([Datum])-[WeekdayID]+11),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "KWID Teil2", each Number.From(#date(Date.Year(Date.From(Number.From([Datum])+4-[WeekdayID])),1,1))),
    #"Subtraktion eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte3", "Subtraktion", each [KWID Teil1] - [KWID Teil2], type number),
    #"Division eingefügt" = Table.AddColumn(#"Subtraktion eingefügt", "Division", each [Subtraktion] / 7, type number),
    #"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Division eingefügt", "KWID", each [Division]-Number.Mod([Division],1), type text),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte4",{"KWID Teil1", "KWID Teil2", "Subtraktion", "Division"}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Entfernte Spalten",{{"KWID", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte5" = Table.AddColumn(#"Geänderter Typ1", "KW", each "KW "&[KWID]),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte5",{{"QuartalID", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte6" = Table.AddColumn(#"Geänderter Typ2", "Quartal", each "Q"&[QuartalID]),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte6",{{"Year", type text}, {"QuartalID", Int64.Type}, {"Weekday", type text}, {"KW", type text}, {"Quartal", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte7" = Table.AddColumn(#"Geänderter Typ3", "KWYear", each [KWID]&[Year]),
    #"Geänderter Typ4" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte7",{{"KWID", Int64.Type}, {"KWYear", type text}, {"Month", type text}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Geänderter Typ4",{"Datum"},#"Feiertage 2018 im Bundesland Hessen:",{"Datum"},"Feiertage 2018 im Bundesland Hessen:",JoinKind.LeftOuter),
    #"Erweiterte Feiertage 2018 im Bundesland Hessen:" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Feiertage 2018 im Bundesland Hessen:", {"Datum"}, {"Datum.1"}),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Erweiterte Feiertage 2018 im Bundesland Hessen:",{{"Datum.1", "Dat_Feiertag"}}),
    #"Hinzugefügte benutzerdefinierte Spalte8" = Table.AddColumn(#"Umbenannte Spalten1", "isWorkDay", each if [WeekdayID]<6 and [Dat_Feiertag]=null then 1 else 0)
in
    #"Hinzugefügte benutzerdefinierte Spalte8"

WebQuery for Holidays:

let
    Quelle = Web.Page(Web.Contents("https://www.schulferien.org/Feiertage/Feiertage_Hessen.html")),
    Data3 = Quelle{3}[Data],
    #"Geänderter Typ1" = Table.TransformColumnTypes(Data3,{{"Header", type text}, {"Datum", type date}, {"WochentagTag", type text}, {"Name", type text}, {"KW", type text}, {"Urlaub?", type text}, {"Column7", type text}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ1", each ([KW] <> null))
in
    #"Gefilterte Zeilen"

Result, depends on Excel-Based Example Data

t
    Quelle = Excel.Workbook(File.Contents("C:\Users\schaeferb\Desktop\TestZeit.xlsx"), null, true),
    Tabelle1_Table = Quelle{[Item="Tabelle1",Kind="Table"]}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Tabelle1_Table,{{"Erfassung", type datetime}, {"Lösung", type datetime}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "DauerErfassung", each if DateTime.Date([#"Erfassung"])=DateTime.Date([Lösung]) then [Lösung]-[#"Erfassung"] else if DateTime.Time([#"Erfassung"])<=DateTime.Time(#datetime(Date.Year([#"Erfassung"]),Date.Month([#"Erfassung"]),Date.Day([#"Erfassung"]),8,0,0)) then 8/24 else DateTime.Time(#datetime(Date.Year([#"Erfassung"]),Date.Month([#"Erfassung"]),Date.Day([#"Erfassung"]),17,0,0))-DateTime.Time([#"Erfassung"])),
    #"Subtraktion des eingefügten Datums" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Subtraktion", each Duration.Days([Lösung] - [#"Erfassung"])+1, Int64.Type),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Subtraktion des eingefügten Datums",{{"Subtraktion", "Tage"}}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Umbenannte Spalten", "DauerLösung", each if DateTime.Date([#"Erfassung"])=DateTime.Date([Lösung]) then 0 else if DateTime.Time([Lösung])>=DateTime.Time(#datetime(Date.Year([Lösung]),Date.Month([Lösung]),Date.Day([Lösung]),17,0,0)) then 8/24 else DateTime.Time([Lösung])-DateTime.Time(#datetime(Date.Year([Lösung]),Date.Month([Lösung]),Date.Day([Lösung]),8,0,0))),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "DauerZwischen", each if [Tage]>2 then ([Tage]-2)*8/24 else 0),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte2",{{"DauerErfassung", type duration}, {"DauerLösung", type duration}, {"DauerZwischen", type duration}}),
    #"Summe eingefügt" = Table.AddColumn(#"Geänderter Typ1", "Addition", each List.Sum({[DauerErfassung], [DauerLösung], [DauerZwischen]}), type duration),
    #"Datum eingefügt" = Table.AddColumn(#"Summe eingefügt", "Datum", each DateTime.Date([#"Erfassung"]), type date),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Datum eingefügt",{{"Datum", "Dat_Erfasst"}}),
    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(#"Umbenannte Spalten1", "Dates", each ListDates([Dat_Erfasst], [Tage])),
    #"Erweiterte Dates" = Table.ExpandListColumn(#"Aufgerufene benutzerdefinierte Funktion", "Dates"),
    #"Erweiterte Dates1" = Table.ExpandRecordColumn(#"Erweiterte Dates", "Dates", {"currentDate"}, {"currentDate"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Dates1",{"DauerErfassung", "Tage", "DauerLösung", "DauerZwischen", "Lösung"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Entfernte Spalten",{"currentDate", "Erfassung", "Dat_Erfasst", "Addition"}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",{{"currentDate", type date}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Geänderter Typ2",{"currentDate"},Kalender,{"Datum"},"Kalender",JoinKind.LeftOuter),
    #"Erweiterte Kalender" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Kalender", {"isWorkDay"}, {"isWorkDay"}),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Erweiterte Kalender",{{"isWorkDay", Int64.Type}, {"Addition", type number}}),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Geänderter Typ3", "NettoZeit", each if [isWorkDay]=0 then [Addition]-((1/24)*8) else [Addition]),
    #"Geänderter Typ4" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Addition", type duration}, {"NettoZeit", type duration}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ4", {"Erfassung"}, {{"NettoZeit2", each List.Average([NettoZeit]), type duration}}),
    #"Geänderter Typ5" = Table.TransformColumnTypes(#"Gruppierte Zeilen",{{"NettoZeit2", type number}}),
    #"Multiplikation eingefügt" = Table.AddColumn(#"Geänderter Typ5", "Multiplikation", each [NettoZeit2] * 24, type number),
    #"Umbenannte Spalten2" = Table.RenameColumns(#"Multiplikation eingefügt",{{"Multiplikation", "Stunden"}}),
    #"Geänderter Typ6" = Table.TransformColumnTypes(#"Umbenannte Spalten2",{{"NettoZeit2", type duration}}),
    #"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Geänderter Typ6", "InTime", each if [Stunden]<=8 then true else false),
    #"Geänderter Typ7" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte4",{{"InTime", type logical}})
in
    #"Geänderter Typ7"

I'm only able to provide it in German but maybe you can get a clue on whats going on. This is the Result:

From this:

Raw Example Data

To this:

I hope it helps.

Regards

BjoernSchaefer

[Anonymous]

Hey, thanks for your response. Since I am new to Power BI, I can't really figure out what is going wrong. I tried copying and pasting your code within a new column which wasn't doing anything but besides giving error.

Regards.

Shahbaaz

[v-chuncz-msft]

@Anonymous ,

Take a look at The Advanced Editor.