Numeric Range Slicer only use available values

[jeffgarlisch]

Hey All, 

I have a date dimension table with integer values for financial months.

                                                            eg. 201801...201805...201806....201812

What im wondering is if there is any way for me to use this field to drive the numeric range slicer but not have it go up in increments of 1.

                                                                    e.g 201812....201833.....201875

but move to the next value in the range to correspond with the next month in the range. 

Please let me know if you have any questions! 

Thanks in advance!

[JethroTF]

I am having the same issue. Did you ever find a solution to this?

[Omega]

Assuming you are using calendar table,  create the below calculated column: 

Year Month = FORMAT('Calendar'[Date],"YYYYMM")

[pawel1]

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

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

[jeffgarlisch]

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

[edhans]

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.

[edhans]

I don't understand what you are asking. WHat months are 201833 and 201875?

If you want to have alternate values besides months in your slicer, either create a calculated column with your logic in it and use that as the slicer, or create a new column in Power Query with the logic you want and use that.

[jeffgarlisch]

They arent months? 

Thats the issue, when using the numeric yearmonth field in my hardcoded datetable, in the numeric slider slicer, it will increase by increments of 1 and not skip to the next available value in the table. 

example: 

The slicer works like this 

201812......201813......201814 

but it should move up from 201812......201901

or 201799.....201801 

when it should be 201712....201801

Does this make more sense?

[v-juanli-msft]

Hi @jeffgarlisch

Assume you have a dataset like this,

"date" is numeric yearmonth field, but "date2" is the correct month associated with "date1", but "date2" doesn't exsit in your dataset.

Could you show me the first numeric yearmonth and the last numeric yearmonth, so i can analyze the rule to transform from this type numeric yearmonth to the correct date type.

Best Regards

Maggie

[jeffgarlisch]

My Data Looks like this for the month year data, we store date dim data on a daily level so i selected the MIN and MAX of the datevalue column to show the date range of what the month year should be looking at. 

Please let me know if this is what youre looking for. 

[v-juanli-msft]

Hi @jeffgarlisch

From this table, i can't see anything like " 201812....201833.....201875".

Which column do you add in the slicer?

It is not clear for me how this table relate to your original table?

Best Regards

Maggie