Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
To this:
I hope it helps.
Regards
BjoernSchaefer
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
@Anonymous ,
Take a look at The Advanced Editor.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |