fnGetSequentialHalfYearNumber.pq

let
fn = (TableWithDateColumn as table, DateValue as date) as number =>
let
    GetYearHalfYear = Table.AddColumn(TableWithDateColumn , "JahrHalbJahr", each Date.Year([Datum])*100 + (if Date.Month([Datum]) < 7 then 1 else 2) ),
    GetListOfYearQuarter = GetYearHalfYear[JahrHalbJahr],
    ConvertToTable = Table.FromList(GetListOfYearQuarter, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "JahrHalbJahr"}}),
    ChangeType = Table.TransformColumnTypes(RenameColumn,{{"JahrHalbJahr", Int64.Type}}),
    RemoveDuplicates = Table.Distinct(ChangeType),
    AddIndex = Table.AddIndexColumn(RemoveDuplicates, "SequentialHalfYearNumber", 1, 1),
    JoinQueries = Table.NestedJoin(GetYearHalfYear,{"JahrHalbJahr"},AddIndex,{"JahrHalbJahr"},"AddIndex",JoinKind.LeftOuter),
    NextStep = Table.ExpandTableColumn(JoinQueries, "AddIndex", {"SequentialHalfYearNumber"}, {"SequentialHalfYearNumber"}),
    NextStep2 = Table.RemoveColumns(NextStep, {"JahrHalbJahr"}),
    Output = Table.FirstN(Table.SelectRows(NextStep2, 
	  each (Date.Year(DateValue)*100 + (if Date.Month(DateValue) < 7 then 1 else 2) ) = Date.Year(_[Datum])*100 + (if Date.Month(_[Datum]) < 7 then 1 else 2) ),1)[SequentialHalfYearNumber]{0}
in
    Output,
    fnType = type function(
            TableWithDateColumn as (type table meta [Documentation.LongDescription="Dies muss eine Tabelle sein, in welcher die Spalte 'Datum' existiert."]),
            DateValue as (type date meta [Documentation.LongDescription="Hierbei handelt es sich um das Feld 'Datum'"])
         ) as list meta [
                          Documentation.Name = "fnGetSequentialHalfYearNumber",
                          Documentation.LongDescription = 
                            "Diese Funktion erzeugt eine fortlaufende Halbjahresnummer, für die Nutzung in DAX Time Intelligence-Funktionen",
                          Documentation.Author = "Lars Schreiber, www.ssbi-blog.de"
                        ]
in
Value.ReplaceType(fn, fnType)