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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment