cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffgarlisch Regular Visitor
Regular Visitor

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 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!

 

 

 

 

 

 

 

 

 

9 REPLIES 9
edhans New Contributor
New Contributor

Re: Numeric Range Slicer only use available values

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.

Highlighted
pawel1 Member
Member

Re: Numeric Range Slicer only use available values

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

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


YearMonth.JPG

 

 

 

jeffgarlisch Regular Visitor
Regular Visitor

Re: Numeric Range Slicer only use available values

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?

 

 

jeffgarlisch Regular Visitor
Regular Visitor

Re: Numeric Range Slicer only use available values

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

edhans New Contributor
New Contributor

Re: Numeric Range Slicer only use available values

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.

Omega Established Member
Established Member

Re: Numeric Range Slicer only use available values

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

 

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

Re: Numeric Range Slicer only use available values

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.

9.png

 

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 Regular Visitor
Regular Visitor

Re: Numeric Range Slicer only use available values

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. 

 

Capture.JPG

 

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: Numeric Range Slicer only use available values

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