Forum Discussion

jeffgarlisch's avatar
jeffgarlisch
Helper I
7 years ago

Numeric 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...
Need Help
pawel1's avatar
pawel1
Kudo Kingpin
7 years ago

Do you use this simple formula to come to 'YearMonth' column?

YearMonth = 'Date'[Year]*100+'Date'[Month]


 

 

 

  • jeffgarlisch's avatar
    jeffgarlisch
    Helper I
    7 years ago

    I use a prebuilt comprehensive date dimensions lookup table in my database

    • edhans's avatar
      edhans
      Community Champion
      7 years ago

      Your 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.