Forum Discussion
Advocate IIAutomatic update Month in Filter
Hi all,
I have a dashboard with filter by year and months
This dashboard is connected with multidimension cubes (was created and managed in SQL Server), so connecting live in Power BI all the functions such as data type or calculated columns are hidden.
I want the month is updated automatically to current month (at 2nd date of month - means at 2nd date of month, the filter month will change month automatically to its current month). I used "relative date" filter type, and "is in this - month". However, it will show in dropdown list only this current month. What if my users still want to select back the other old months in filter?
Have we any solution that the month (in filter) will updated automatically month and we still have available drop down list with all of months to select. For example: today is 2nd of February, so the month in filter will change & saved automatically to February, but in this list still have full list of month from Jan - Dec
Thanks for your supports!
Best regards,
Thu
Hi, ThuJa23
Your needs can't be implemented directly, but there is a workaround.
Like this:
Create a custom slicer column and use it as slicer.
Slicer = IF(MONTH([Date])=MONTH(TODAY())&&YEAR([Date])=YEAR(TODAY()),"This year month",FORMAT([Date],"mmmm yyyy"))I also create a column for sort.
Below is my sample.
There are other ways, but I think it is more troublesome. After creating, you need to filter each visual. You can refer to it and choose the way you like:
Solved: Slicer to select current month as default - Microsoft Power BI Community
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
4 Replies
- v-janeyg-msft
Community Support
Hi, ThuJa23
Your needs can't be implemented directly, but there is a workaround.
Like this:
Create a custom slicer column and use it as slicer.
Slicer = IF(MONTH([Date])=MONTH(TODAY())&&YEAR([Date])=YEAR(TODAY()),"This year month",FORMAT([Date],"mmmm yyyy"))I also create a column for sort.
Below is my sample.
There are other ways, but I think it is more troublesome. After creating, you need to filter each visual. You can refer to it and choose the way you like:
Solved: Slicer to select current month as default - Microsoft Power BI Community
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
ThuJa23Hi v-janeyg-msft ,
Thanks for your replies
The problem to me is I cannot use function create "New Column" in my report, as I said we do live connect with SQL multidimension cube. The only function I can use is only Creat New Measure.
Do you have another way that I can use Measure / DAX to create this new slicer?
Thanks so much
Best regards,
Thu
- v-janeyg-msft
Hi, ThuJa23
There is no good way because LC mode restricts a lot.
If you don't mind using two slicers, You can try:
Measure = if(VALUES('Table'[Date].[MonthNo])=MONTH(TODAY())&&VALUES('Table'[Date].[Year])=YEAR(TODAY()),1,0)One slicer for this year's month using the measure in visual filter pane, another slicer for all years' months.
Turn off the interaction between the two slicers, and the two slicers can't be used together.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
