Power Query Geschäftsjahr und -quartal aus Datum ableiten

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.Year([Date])-1) & "/" & Number.ToText(Date.Year([Date])) else Number.ToText(Date.Year([Date])) & "/"& Number.ToText(Date.Year([Date])+1)

Ansatz 3:

benutzerdefinierte Funktion implementieren:

wiederverwendbare benutzerdefinierte Funkton (custom function)
Folgenden Code in eine neue, leere Abfrage einfügen und Abfrage in fxGeschaeftsjahr umbenennen

--- SCHNIPP

let
fxGeschaeftsjahr = (Datum as date, GJErsterMonat as number) =>
let
FY1 = if Date.Month(Datum) >= GJErsterMonat and Date.Month(Datum) <= 12 then Text.From(Date.Year(Datum)) else Text.From(Date.Year(Datum) - 1),
FY2 = if Date.Month(Datum) >= GJErsterMonat and Date.Month(Datum) <= 12 then Text.From(Date.Year(Datum) + 1) else Text.From(Date.Year(Datum)),
FY = FY1 & "/" & FY2
in
FY
in
fxGeschaeftsjahr

--- SCHNAPP ---

wiederverwendbare benutzerdefinierte Funktion (custom function) alternative Syntax

--- SCHNIPP

(Datum as date, GJErsterMonat as number) =>
let
FY1 = if Date.Month(Datum) >= GJErsterMonat and Date.Month(Datum) <= 12 then Text.From(Date.Year(Datum)) else Text.From(Date.Year(Datum) - 1),
FY2 = if Date.Month(Datum) >= GJErsterMonat and Date.Month(Datum) <= 12 then Text.From(Date.Year(Datum) + 1) else Text.From(Date.Year(Datum)),
fxGeschaeftsjahr = FY1 & "/" & FY2
in
fxGeschaeftsjahr

--- SCHNAPP

Alternative mit Excel Formeln:

=WENN(UND(MONAT(A1)>=4;MONAT(A1)<=12);TEXT(JAHR(A1);"@") & " / " & TEXT(JAHR(A1)+1;"@");TEXT(JAHR(A1)-1;"@") & " / " & TEXT(JAHR(A1);"@"))

Geschäftsquartal als

• [benutzerdefinierte Spalte]

In diesem Beispiel ist das Geschäftsjahr um 1 Quartal zum Kalenderquartal versetzt

if Date.QuarterOfYear([Datum])-1 = 0 then "Q4" 

else "Q"& Number.ToText(Date.QuarterOfYear([Datum])-1)

• wiederverwendbare Funktion (in neue Abfrage kopieren)

--- SCHNIPP

(Datum as date) =>
let
//im Beispiel beginnt das Geschäftsjahresquartal im 2ten Kalenderquartal
FQ = if Date.QuarterOfYear(Datum) - 1 = 0 then "Q4" else "Q" & Text.From(Date.QuarterOfYear(Datum) - 1)
in
FQ

--- SCHNAPP

alternativer Ansatz

---SCHNIPP

(Datum as date) =>

let

    Quelle = Date.Month((Datum)),

    //Geschäftsjahr beginnt am 01.04.XXXX, Q1 = 01.04.XXXX 

    Quelle2 = if Quelle >= 4 and Quelle <=12 then Number.RoundUp(Quelle/3,0) - 1 else 4,

    Quelle3 = "Q" & Text.From(Quelle2)

in

    Quelle3

---SCHNAPP

GJRelativZuHeute

letztes, aktuelles und nächstes Geschäftsjahr ausgehend von Auswertungszeitpunkt ermitteln

--- SCHNIPP ---

(tbl as table, dtDatum as text) => 

let

    // Aktuelles Datum abrufen

    AktuellesDatum = DateTime.LocalNow(),

    // Monat und Jahr des aktuellen Datums abrufen

    AktuellerMonat = Date.Month(AktuellesDatum),

    AktuellesJahr = Date.Year(AktuellesDatum),

    // Geschäftsjahr Anfang und Ende festlegen

    //GJ Start Monat = 4

    //GJ Ende Monat = 3

    GeschäftsjahrAnfang = if AktuellerMonat >= 4 then #date(AktuellesJahr, 4, 1) else #date(AktuellesJahr-1, 4, 1),

    GeschäftsjahrEnde = if AktuellerMonat >= 4 then #date(AktuellesJahr + 1, 3, 31) else #date(AktuellesJahr, 3, 31),

    // vorvorletztes Geschäftsjahr berechnen

    letztesGeschäftsjahrAnfang = if AktuellerMonat >= 4 then Date.AddYears(GeschäftsjahrAnfang, -2) else Date.AddYears(GeschäftsjahrAnfang, -1),

    letztesGeschäftsjahrEnde = if AktuellerMonat >=4 then Date.AddYears(GeschäftsjahrEnde, -2) else Date.AddYears(GeschäftsjahrEnde, -1),

    // Nächstes Geschäftsjahr berechnen

    NächstesGeschäftsjahrEnde = Date.AddYears(GeschäftsjahrEnde, 1),

    // Datentabelle referenzieren (Beispielname: "Source")

    Quelle = tbl, // Ersetze "Source" durch den Namen deiner Datentabelle

    // Datensätze nach Datum filtern

    GefilterteDaten = Table.SelectRows(Quelle, each Record.Field(_,dtDatum) >= letztesGeschäftsjahrAnfang and Record.Field(_,dtDatum) <= NächstesGeschäftsjahrEnde)

in

    GefilterteDaten

--- SCHNAPP ---

siehe auch Datumsfunktionen mit Power Query / Language M