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.
Hi guys,
I have a month filter in my dashboard . I'd like to display "current month" instead of jun 2017 for the last month. How do I do that?
Thanks,
Carlos
Solved! Go to Solution.
I would add a column to your Calendar table that contains month-year text for all months except the "current" month where it contains "Current Month".
Also, ensure that this column is sorted by a numerical month-year column.
Here is an example of a Calendar table created with DAX but probably should be done with M.
Calendar = VAR StartDate = DATE ( 2016, 1, 1 ) VAR EndDate = DATE ( 2017, 06, 30 ) VAR YearMonthNumberFinal = YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1 // This sets the final YearMonth based on the last date in the calendar. // You could define however you want, e.g. based on TODAY() VAR BaseCalendar = CALENDAR ( StartDate, EndDate ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR MonthName = FORMAT ( BaseDate, "mmmm" ) VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", MonthName, "Year Month Number", YearMonthNumber, "Year Month", YearMonthName, "Year Month with Current Month", IF ( YearMonthNumber = YearMonthNumberFinal, "Current Month", YearMonthName ) ) )
Regards,
Owen
No problem!
For text columns, you just need to specify an appropriate "Sort by" column.
In the case of "Year Month with Current Month", select it in either the field list or Data View, then go Modelling => Sort by Column and choose Year Month Number.
(This is already done in the dropbox link I posted)
I would add a column to your Calendar table that contains month-year text for all months except the "current" month where it contains "Current Month".
Also, ensure that this column is sorted by a numerical month-year column.
Here is an example of a Calendar table created with DAX but probably should be done with M.
Calendar = VAR StartDate = DATE ( 2016, 1, 1 ) VAR EndDate = DATE ( 2017, 06, 30 ) VAR YearMonthNumberFinal = YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1 // This sets the final YearMonth based on the last date in the calendar. // You could define however you want, e.g. based on TODAY() VAR BaseCalendar = CALENDAR ( StartDate, EndDate ) RETURN GENERATE ( BaseCalendar, VAR BaseDate = [Date] VAR YearDate = YEAR ( BaseDate ) VAR MonthNumber = MONTH ( BaseDate ) VAR MonthName = FORMAT ( BaseDate, "mmmm" ) VAR YearMonthName = FORMAT ( BaseDate, "mmm yy" ) VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1 RETURN ROW ( "Year", YearDate, "Month Number", MonthNumber, "Month", MonthName, "Year Month Number", YearMonthNumber, "Year Month", YearMonthName, "Year Month with Current Month", IF ( YearMonthNumber = YearMonthNumberFinal, "Current Month", YearMonthName ) ) )
Regards,
Owen
Hello @OwenAuger
This YearMonthNumberFinal YEAR ( EndDate ) * 12 + MONTH ( EndDate ) - 1 , what is this number you are generating?
Thanks, N -
I should mention that the pattern in the DAX code was borrowed from
http://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/
YearMonthNumber is an integer that increments by one for each month.
It can be useful as a "sort by" column for months, or for calculating differences between dates in whole numbers of months.
Thanks Owen but it is not working. I got the message: "Multiple Columns can`t be converted to scalar value"
It sounds like you tried to create a measure with this code rather than a calculated table.
Go to Modelling tab => New Table and paste in the code.
Here is a sample in case it helps:
https://www.dropbox.com/s/wnll2q2glarwbb0/Calendar%20with%20current%20month%20sample.pbix?dl=0
Regards,
Owen
Thanks Owen!
It`s working but not sorting by date because the data have to be classified as text instead of date. Do you know how to solve that?
I wish to use the 'Year Month with Current Month' to create a filter.
Many Thanks
No problem!
For text columns, you just need to specify an appropriate "Sort by" column.
In the case of "Year Month with Current Month", select it in either the field list or Data View, then go Modelling => Sort by Column and choose Year Month Number.
(This is already done in the dropbox link I posted)
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |