Forum Discussion
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!
10 Replies
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.
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?
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
Do you use this simple formula to come to 'YearMonth' column?
YearMonth = 'Date'[Year]*100+'Date'[Month]I use a prebuilt comprehensive date dimensions lookup table in my database
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.
Assuming you are using calendar table, create the below calculated column:
Year Month = FORMAT('Calendar'[Date],"YYYYMM")I am having the same issue. Did you ever find a solution to this?
