Instantly share code, notes, and snippets.

Embed
What would you like to do?
let
fn = (TableWithDateColumn as table, DateValue as date) as number =>
let
GetYearQuarter = Table.AddColumn(TableWithDateColumn , "JahrQuartal", each Date.Year([Datum])*100+Date.QuarterOfYear([Datum])),
GetListOfYearQuarter = GetYearQuarter[JahrQuartal],
ConvertToTable = Table.FromList(GetListOfYearQuarter, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "JahrQuartal"}}),
ChangeType = Table.TransformColumnTypes(RenameColumn,{{"JahrQuartal", Int64.Type}}),
RemoveDuplicates = Table.Distinct(ChangeType),
AddIndex = Table.AddIndexColumn(RemoveDuplicates, "SequentialQuarterNumber", 1, 1),
JoinQueries = Table.NestedJoin(GetYearQuarter,{"JahrQuartal"},AddIndex,{"JahrQuartal"},"AddIndex",JoinKind.LeftOuter),
NextStep = Table.ExpandTableColumn(JoinQueries, "AddIndex", {"SequentialQuarterNumber"}, {"SequentialQuarterNumber"}),
NextStep2 = Table.RemoveColumns(NextStep, {"JahrQuartal"}),
Output = Table.FirstN(Table.SelectRows(NextStep2, each (Date.Year(DateValue)*100 + Date.QuarterOfYear(DateValue)) = Date.Year(_[Datum])*100 + Date.QuarterOfYear(_[Datum])),1)[SequentialQuarterNumber]{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 = "fnGetSequentialQuarterNumber",
Documentation.LongDescription =
"Diese Funktion erzeugt eine fortlaufende Quartalsnummer, 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