Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
I am having the same issue. Did you ever find a solution to this?
Assuming you are using calendar table, create the below calculated column:
Year Month = FORMAT('Calendar'[Date],"YYYYMM")
Do you use this simple formula to come to 'YearMonth' column?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThey 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?
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |