The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
The client has data which is updated monthly and the dashboard contains a chart and a slicer which is set to the current month. They would like the slicer to change the default selection when the data for the new month is loaded. For example, the current month selected in the slicer is January, and they would like the month selected change to February when that datat is loaded so the corresponding chart will show the data for the latest month.
I am drawing a blank as to how you would make this happen. Can someone point me in the right direction?
Solved! Go to Solution.
You can't do this directly in the report designer, but you should be able to do some modelling to make it work. See the sample .pbix file here.
Basically you make a new field based on the following bit of Power Query M:
if Date.IsInCurrentMonth( [Date] ) then "Current Month" else [Month]
Then you set the slicer to "Current Month", when the month changes and the dataset is refreshed, the slicer's "Current Month" selection can remain the same. What "Current Month" actually refers to has changed, but not the literal slicer selection.
As a bonus, you get a sneak peek at a few snippets from my personal work-in-progress ultimate date table in that .pbix. I'll be publishing the full version once I finalize.
hi!
great idea!
is there any way to show both "Current Month" and the value of the month?
i want the default selection will be 'Last 4 weeks' but i want to let the user the option to select the last one week only,
in this way it's not available
thanks!
@greggyb hi is there any way thru which i can get last day,
in my data file last date is 10 june where as in date table its showing 18 july 2016.
Also Please let me know how can i make last day as a default day like i said 10 jun was the last date in my data table so how come i put it to default date.
we have current month, year ,week. function in power query but we dont have current day function.
This also seems like the right solution for me - and thanks for your answer to my other post ....but I can't follow where/how to paste the code and can't access your pbx file. is there an online Power BI tutorial you can point me to where I can see it illustrated? If i paste your formula into the formula bar for a new column it appears to have an issue with the square brackets. thanks again.
@jbowler, the code I shared is from Power Query, which you can access using the Edit Queries ribbon menu item. There's a self-paced edX course designed by Microsoft, with its own forum in this community.
If you publish this report to power BI service while the current month in the slicer is selected then open the report form the power BI report which you just published to https://msit.powerbi.com after logging in to it. Then the slicer is not visible .You cannot see any of the data but if you click on "..." and export to CSV you will see there is data but the slicer with selected value will not visible at all. What is the workaround to fix such kind of issue?
Any idea on this is appreciated. Here is the print screen
What a clever idea. Thanks for the tip, I never would have thought of that. Sending the code was most appreciated too.
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |