Forum Discussion
jeffgarlisch
Helper I
7 years agoNumeric Range Slicer only use available values
Hey All, I have a date dimension table with integer values for financial months. eg. 201801...201805...201806....201812 What i...
jeffgarlisch
Helper I
7 years agoI use a prebuilt comprehensive date dimensions lookup table in my database
edhans
Community Champion
7 years agoYour YYYYMM should be a calculation in your Date table. See the following M code.
let
Source = {Number.From(#date(2018,1,1))..Number.From(#date(2018,12,31))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "YYYYMM", each Date.Year([Date]) * 100 + Date.Month([Date]), Int64.Type)
in
#"Added Custom"That last row -
Date.Year([Date]) * 100 + Date.Month([Date]
as a column will create 201801, 201802, etc. It will never create a bogus YYYYMM like 201875 or 201899.
Use that column as your slicer.