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.
Hello,
In my calendar table, I have Jan 2015 until Dec 29018.
I would like to put a filter (slicer) on the page that shows only (!) JAN18 until current Month,
I tried to create a new calculated column with Filter and VALUES but it doesn't work. I get an empty column.
Months 2018 = CALCULATE(VALUES(dimCalendar[MonthYear]),FILTER(dimCalendar, dimCalendar[Year] = 2018))
Thanks in advance for any help,
Tamir
Solved! Go to Solution.
Hi@Tamir
For value (blank) in this column may have many different corresponding values in other columns, So it can’t be sorted by another column.
And filter the whole report/page by this column is just to hide the "Blank" in the visual you don’t want to see, so you can just add a visual level filter on this visual.
Best Regards,
Lin
Column = IF ( MONTH ( 'Table'[Date] ) <= MONTH ( TODAY () ) && YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ), FORMAT ( 'Table'[Date], "mmmm" ) & " " & YEAR ( 'Table'[Date] ) )Result:
Your DAX solution is wonderful.
However, How can I sort it by months and not alphabetically?
Using the "Sort By" command in the Modelling produce an error due to the empty cells for future months.
I could format the months as "mm" and get the form of 01,02...etc... but I do want the names (three letters).
Also, how can I hide the "Blank" months (the future)?
Any idea?
Thank you,
Regards,
Tamir
Hi@Tamir
Q1:
You can format the months as "mmm" and get the names and the form of three letters.
Q2:
This error is not caused by the empty cells of the future months,
It could be caused by the duplicates in “Months Months Names YTD”.
For example :
We sort Index by Column or Column 2, it’s OK.
But soft Custom by Column or Column 2, it will be wrong.
Q3:
if you want to hide the "Blank" months, you can drag Column field into Page level filters and filter (Blank) like below:
Best Regards,
Lin
Q3 and Q1 - Great. Did it.
Q2 - I don't understand. As in your screenshot, I would like to sort column2 ("mmm-yy") by the Index column (in my dimCalendar it would be column MonthYearNumber).
Thank you,
Regards,
Tamir
Hi@Tamir
After my test by adding Column Months Name YTD and Column Year Month Number and sort Months Name YTD by Year Month Number like you,
So could you please share your pbix or the data sample and formula of your Column Months Name YTD and Column Year Month Number.
You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Please find a link to a report with the problem I face.
Regards
Tamir
Hi@Tamir
After my test on your demo , value ”Blank” in column “Months to Date” has many different corresponding values in column “MonthYearNum”,
So it occurred this error.
You can try to do these follow my steps:
Step 1:
Add two columns:
One is to filter :
Months to Date(filter) = IF ( MONTH ( 'Calendar'[Date] ) <= MONTH ( TODAY () ) && YEAR ( 'Calendar'[Date] ) = YEAR ( TODAY () ), FORMAT ( 'Calendar'[Date], "mmm" ) & "-" & FORMAT ( 'Calendar'[Date], "yy" ), BLANK () )
One is to be sorted and dragged into visual:
Months to Date =FORMAT ( 'Calendar'[Date], "mmm" ) & "-" & FORMAT ( 'Calendar'[Date], "yy" )
Step 2:
Sort Months to Date by MonthYearNum and drag it into visual
drag Months to Date(filter) into slicer and page level filter.
here is Demo, please try it .
https://www.dropbox.com/s/v80cfe7eahr4mws/Sort%20Month%20to%20Date.pbix?dl=0
Reference: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Best Regards,
Lin
Thank you @v-lili6-msft
I was wondering if it possible to sort the column
Months to Date(filter)
by another column.
I do not want to filter the whole report/page by that column as other visuals need to be shown for other dates as well.
Thank you,
Tamir
Hi@Tamir
For value (blank) in this column may have many different corresponding values in other columns, So it can’t be sorted by another column.
And filter the whole report/page by this column is just to hide the "Blank" in the visual you don’t want to see, so you can just add a visual level filter on this visual.
Best Regards,
Lin
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |