Excel im betrieblichen Umfeld

Als Datenquelle fungiert Website  https://www.arbeitstage.org/  neue, leere Abfrage erstellen, Language M Code einfügen und in fnGetDeutscheFeiertageJeBundesland umbenennen: --- SCHNIPP Power Query Language M Code --- let fnAlleFeiertage = (Kalenderjahr as number) as table =>    let      //Liste aller Bundesländer für den Aufbau der URL bei arbeitstage.org      List_Bundeslaender = {          "baden-wuerttemberg",           "bayern", "berlin",           "brandenburg",           "bremen",           "hamburg",           "hessen",           "mecklenburg-vorpommern",           "niedersachsen",           "nordrhein-westfalen",           "rheinland-pf...

Periodenvergleiche können mit Excel Power Pivot mit der DAX Funktion DATEADD() umgesetzt werden Beispiel [Faktentabelle] 1 [Datumstabelle] mit Power Pivot erstellen 2 [Faktentabelle] und [Datumstabelle] über Datumsfeld verknüpfen 3 Measures in Faktentabelle anlegen Wert_Vorjahr:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; YEAR ) ) Wert_Vorvorjahr:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -2; YEAR ) ) Wert Vorquartal:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; QUARTER ) ) Wert Vormonat:=CALCULATE ( SUM ( Tabelle1[Wert] ); DATEADD ( Kalender[Date]; -1; MONTH ) ) 4 Ergebnis (Pivottabelle, vereinfachte Pivottabelle) Quelle https://powerpivotinsights.de/dateadd/ alternativ (getestet mit Power BI Desktop) DAX  YoY(Jahresvergleich) =   var _prev =   IF (   NOT ( ISBLANK ( [Umsatz] )),   CALCULATE ( [Umsatz] , PREVIOUSYEAR ( Kalender_DAX [Date] )  ))   RETURN...

Lern Video Definition der 1. Woche des Jahres USA := Beginn 1. Kalenderwoche immer mit 1. Januar. Deutschsprachigen Raum := ISO-Kalenderwoche üblich. Woche 1 = Woche im Jahr, die den ersten Donnerstag des Jahres enthält. Unterschiedliche Systematiken für die Zuordnung der Kalenderwochen zu einem Kalendermonat 4-5-4 Methode , Gemeinjahr (Nicht-Schaltjahr) = 4 gleichlange Quartale. Jedes Quartal besteht aus drei Monaten, von denen der e rste 28 Tage („4“ Wochen) , der zweite 35 Tage („5“ Wochen) und der dritte wieder 28 Tage („4“ Wochen) umfasst. 5-4-4 Methode , Gemeinjahr (Nicht-Schaltjahr) = 4 gleichlange Quartale. Jedes Quartal besteht aus drei Monaten, von denen der erste 35 Tage („5“ Wochen) , der zweite 28 Tage („4“ Wochen) und der dritte wieder 28 Tage („4“ Wochen) umfasst. 13-Monate-Kalender-Methode , Annahme = Monate beinhalten exakt 4 Wochen (28 Tage). Datumsbezogene Funktionen, welche Texte zurückgeben Date.DayOfWeekName([Datum], culture ) Datum form...

Angenommen, man will in Abhängigkeit eines Datums immer den 10ten des Folgemonats in einer neuen Spalte zurückgeben. Die relevante Excel Formel hierfür lautet =DATUM(JAHR(E2);MONAT(E2)+1;10) In Power Query / Funktionen sucht man jedoch vergebens nach einer äquivalenten Funktion. Die Lösung ist das Literal "#" =#date(Date.Year(Date.AddMonths([Datum],1)),Date.Month(Date.AddMonths([Datum],1)),10) Ergebnis weiterführende Informationen

Power Query bringt einen neuen Datentyp mit, mit dessen Hilfe auf Basis von datetime Werten (Datum und Uhrzeit) komfortabel zeitliche Differenzen in Tagen, Stunden, Minuten und Sekunden berechnet werden können. Folgendes Beispiel soll die Anwendung dieses Datentyps verdeutlichen: Ausgangstabelle Duration Datentyp (hier = Spalte [Differenz] = Differenz zweier datetime Werte [Datum], [End Datum] Duration Datentyp (= Differenz von datetime Werten) Tage := Duration.Days() Minuten := Duration.Minutes() Sekunden := Duration.Seconds() Lern Video

Mit folgender benutzerdefinierten Funktion kann die Anzahl der Tage (Montag, Dienstag, Mittwoch, Donnerstag, Freitag, Samstag oder Sonntag) zwischen 2 Datumswerten (Startdatum, Enddatum) ermittelt werden 1 neue Abfrage in Power Query erstellen und Language M Code einfügen über Ansicht - erweiterter Editor) 2 sprechenden Namen für die Funktion vergeben (zB AnzahlTageZwischenDatumswerten) 3 benutzerdefinierte Spalte einfügen und Funktion verwenden, 2 Parameter [Start], [End] --- SCHNIPP --- let     Source = (Start as date, End as date) => let         Source = List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0)),         Custom1 = List.Select(Source, (_)=>Date.DayOfWeek(_, Day.Monday) = 0),         #"Calculated Count" = List.NonNullCount(Custom1)     in         #"Calculated Count" in     Source --- Schnapp --- Im genannten Beispiel wir...

Stellen Sie sich vor, Sie sind ein Doktor und haben mehrere Folgetermine mit einem Patienten auf Basis eines Starttermins. Um diese Folgetermine zu planen kann mit Hilfe von Power Query folgendermaßen vorgegangen werden: 1 Tabelle mit Terminen in Power Query laden 2 neue benutzerdefinierte Spalte [Folgetermine] erstellen = List.Dates(Date.AddDays([Ersttermin],[#"Häufigkeit (alle x Tage)"]),[Anzahl Folgetermine],Duration.From([#"Häufigkeit (alle x Tage)"])) 3 Erweitern der Spalte [Folgetermine] 4 Ergebnis weiterführende Informationen siehe hier siehe auch Datumswerte zwischen Datumswerten auffüllen

Im Folgenden wird eine Methode mit Power Query beschrieben, wie man ausgehend von Startdatum, Startuhrzeit / Enddatum, Enduhrzeit zeitliche Differenzen (Kalender-, Arbeitstagen, Stunden, Minuten) berechnen kann Wie man optional eine Liste mit Feiertagen erstellen kann sehen Sie hier Language M Code --- SCHNIPP --- let fxArbeitstage = (start as date, end as date, optional Feiertage as list) as number =>         let            Liste_Feiertage = if Feiertage = null then {} else Feiertage,            Liste_Tage = {Number.From(start)..Number.From(end)},            Liste_Differenz  = List.Difference(Liste_Tage, Liste_Feiertage),            Liste_Mod = List.Transform(Liste_Differenz, each Number.Mod(_, 7)),            Liste_Sel = List.Select(Liste_Mod, each _>1),       ...

Im Folgenden wird eine Methode beschrieben, wie man anhand eines Datums ein relatives Datum ableiten kann 1 Liste mit Datumswerten als Abfrage abbilden 2 benutzerdefinierte Spalte hinzufügen, Name [relativer_Monat]    =(Date.Year([Datum])-1)*12+Date.Month([Datum])-((Date.Year(DateTime.LocalNow())-1)*12+ Date.Month(DateTime.LocalNow())) 3 benutzerdefinierte Spalte hinzufügen, Name [Datumsfilter]   =if [relativer_Monat] > 0 then "zukünftig" else if [relativer_Monat] = 0 then "laufender Monat" else if [relativer_Monat] >= -1 then "letzter Monat" else if [relativer_Monat] >= -3 then "letzte 1-3 Monate" else if [relativer_Monat] >= -6 then "letzte 4-6 Monate" else if [relativer_Monat] >= -9 then "letzte 7-9 Monate" else if [relativer_Monat] >= -12 then "letzte 10-12 Monate" else "1 Jahr und länger zurück" weiterführende Informationen siehe hier Lern Video

Mit folgendem Language M Code (Power Query, neue leere Abfrage) kann eine Feiertagsliste erstellt werden: --- SCHNIPP --- (Jahr) => let Neujahr = Number.From(DateTimeZone.From("01.01." & Text.From(Jahr))), ErsterMai = Number.From(DateTimeZone.From("01.05." & Text.From(Jahr))), Weihnachtstag1 = Number.From(DateTimeZone.From("25.12." & Text.From(Jahr))), Weihnachtstag2 = Number.From(DateTimeZone.From("26.12." & Text.From(Jahr))), Ostersonntag= Number.Round ( Number.From ( Number.From ( Date.From ( DateTimeZone.From("01.04."&Text.From(Jahr)) ), type date ), Int64.Type )/7 + Number.Mod ( 19*Number.Mod(Jahr,19)-7,30 )*0.14 ,0 )*7-6, Karfreitag = Ostersonntag-2, Ostermontag = Ostersonntag+1, ChristiHimmelfahrt = Ostersonntag+39, Pfingstmontag = Ostersonntag+50, Feiertagsliste= Table.FromList ( { [A="Neujahr", B=Neujahr], [A="Karfreitag", B=Karfreitag], [A="...

Ausgangstabelle -> Zieltabelle (Logik in Power Query Abfrage) 1 Abfrage erstellen [Bestelldatum] (Daten -> aus Tabelle) 2 Datentyp [Bestelldatum] ändern -> Datum 3 benutzerdefinierte Spalte hinzufügen    TageZwischen = Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Belegdatum])) 4 Schließen und in neue Tabelle laden Tage zwischen Heute (im Beispiel 06.06.2017) und [Bestelldatum] werden in berechenter Spalte [TageZwischen] ausgegeben

Um Arbeitstage in Kalendertage umrechnen zu können, kann man mit Power Query den Modulo Operator zum Einsatz bringen: benutzerdefinierte Spalte hinzufügen, [KT] = Number.RoundDown([AT]/5,0)*7+Number.Mod([AT],5) Quelle: siehe Lars Schreiber  https://ssbi-blog.de/ Feiertagsliste erstellen Deutsche Feiertage je Bundesland (Funktion)

Lern Video Ausgangstabelle: Tabellenname: Tabelle1 Ergebnis: optional können weitere Feiertage in einer Tabelle "Feiertage" (Spalte = Feiertage) erfasst werden, welche bei der Berechnung der Arbeitstage berücksichtigt werden Language M Code (Power Query -> erweiterter Editor -> einfügen) let     fxArbeitstage = (start as date, end as date, optional Feiertage as list) as number =>         let            Liste_Feiertage = if Feiertage = null then {} else Feiertage,            Liste_Tage = {Number.From(start)..Number.From(end)},            Liste_Differenz  = List.Difference(Liste_Tage, Liste_Feiertage),            Liste_Mod = List.Transform(Liste_Differenz, each Number.Mod(_, 7)),            Liste_Sel = List.Select(Liste_Mod, each _>1),           ...

Wie man ein Geschäftsjahr mit Power Pivot DAX aus einem Datum ableiten kann, ist in folgendem Artikel beschrieben. Ein Geschäftsjahr kann bei Bedarf bereits schon während des ETL-Prozesses mit Power Query abgeleitet werden, um dieses dann dem Power Pivot Modell zur Verfügung zu stellen. Im gewählten Beispiel beginnt das Geschäftsjahr am 1.4. und endet im folgenden Kalenderjahr am 31.3: Ansatz 1: 3 neue benutzerdefinierte Spalten anlegen (Referenzspalte "Date") GJ1 = if Date.Month([Date]) >= 4 and Date.Month([Date]) <= 12 then Text.From(Date.Year([Date])) else Text.From(Date.Year([Date]) - 1) GJ2 = if Date.Month([Date]) >= 4 and Date.Month([Date]) <= 12 then Text.From(Date.Year([Date])+1) else Text.From(Date.Year([Date])) GJ = [GJ1]&"/"&[GJ2] Ansatz 2: 1 neue benutzerdefinierte Spalte anlegen (Referenzspalte "Date") GJ = if Date.Month([Date])=1 or Date.Month([Date])=2 or Date.Month([Date])=3 then Number.ToText(Date....

Zuerst eine Excel Tabelle mit Start und Enddatum erstellen. In diesem Fall soll unsere finale Datumstabelle automatisch mit dem heutigen Tag (Funktion HEUTE() ) beginnen. Das Startdatum und Enddatum kann natürlich beliebig abgeändert werden. Danach wird aus der Tabelle eine Power Query Abfrage erstellt (Reiter Power Query, von Tabelle) Über den Funktions Button eine neue Transformationsregel anlegen = {Number.From(Quelle[Wert]{0}) .. Number.From(Quelle[Wert]{1})} Diese Regel erstellt eine Liste (Spaltenname List), welche alle Datumswerte begrenzt durch gewähltes Start- und Enddatum enthält. Im nächsten Schritt auf die Spalte List drücken (rechte Maustaste) und "in Tabelle" auswählen. Spaltenbeschriftung auf "Datum" ändern, auf Datentyp Datum (Reiter Transformieren, Datentyp:Datum) ändern sowie eine Kopie der Spalte (für Wochentage) erstellen Über Reiter "Transformieren, Datum, Tag, Wochentag" wird der Wochentag des Datums ermittel...

Wer regelmäßig mit Power Pivot und mehrdimensionalen Modellen arbeitet kennt die Situation: Man benötigt eine lückenlose Datumstabelle für eine Datumsdimension, doch woher nehmen ? Mit Power Query kann man diese sehr einfach erstellen. Reiter Power Query -> aus anderen Datenquellen -> leere Abfrage erstellen Im Abfrage Editor, Reiter Start (oder Ansicht), in "Erweiterter Editor" wechseln Hier folgenden von Matt Allington entwickelten Quellcode einfügen: let Source = List.Dates, InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)), TableFromList = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}), InsertedCustom = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertedCustom1 = Table.AddColumn(InsertedCus...

Vor allem in einem Reporting Szenario mit Excel können folgende Datumsfunktionen sinnvoll eingesetzt werden: siehe auch dynamischen Jahreskalender erstellen Wochenende anhand Datum ermitteln YTD (Year to Date) kurze Einführung in Power Pivot Kalenderwoche anhand Datum ableiten Quartal / Geschäftsjahr anhand Datum ableiten

Year to Date Wert mit Funktion BEREICH.VERSCHIEBEN() Bezug:   Zelle B2, erster Monatswert. Hier beginnt der Bereich für die Summierung. Zeilen: hier könnte der Startpunkt von B2 nach oben oder unten verschoben werden. Hier nicht relevant. Spalten: hier könnte der Startpunkt von B2 nach links oder rechts verschoben werden. Hier nicht relevant. Höhe: wir möchten “eine Zeile hoch” summieren, daher Wert 1. Breite: der wichtigste Parameter. Wir möchten zwischen 1 und 12 Felder breit summieren. Daher Bezug auf Zelle A2 = aktueller Monat Funktion =TEXT(HEUTE();"M") siehe auch interaktive Charts BEREICH.VERSCHIEBEN()

  Lern Video Mit den Excel Formel DATUM, ZEILE, SPALTE kann man sich quick & dirty einen Jahreskalender bauen: DATUM(JAHR, MONAT,TAG) z.B. DATUM(2015,2,14) = 14.02.2015 ZEILE() gibt den aktuellen Zeilen-, SPALTE() den Spaltenindex zurück. Schreibt man die Jahreszahl in Spalte A1, kann man mit folgender Excel Funktion schnell einen Jahreskalender basteln: =WENN(MONAT(DATUM($A$1;SPALTE();ZEILE()-1))=SPALTE();DATUM($A$1;SPALTE();ZEILE()-1);"") Formel einfach 12 mal nach rechts (Monate) und 31 mal nach unten ziehen, et voila ! Gibt man nun in Spalte A1 ein anderes Jahr ein passt sich der Kalender dynamisch an. Will man die Wochenenden zusätzlich farblich markieren, siehe folgenden Post .

Manchmal ist es sinnvoll, Wochentage kenntlich zu machen. Hierfür bietet Excel die Formel WOCHENTAG(), Rückgabewert 7 = Samstag, 1 = Sonntag usw. Verbindet mal diese Funktion mit einer benutzerdefinierten Formatierung, so kann man Wochenenden farblich markieren: