Slicer Only Show Month / Year and Filter On That

0
votes

I have a table with a value ReportDate, which is in the format '03/01/2020'. I want to create a slicer in my Power BI report that displays only the format '03/2020' and the user can then select the value of the month and year to display, and it will display the data for that month (regardless of the day value). How would one go about doing this? I know technically I can add a new column in the database table, but unfortunately I do not have access to changes in the database and as such, would like a Power BI solution.

2
powerbipowerbi-desktop
Are you importing data or is it direct query mode? – Jon
Importing the data. I would have liked to do query mode but that isn't an option for us. – A M C

2 Answers

2
votes

In the Power Query Editor create a new column with formula

Date.ToText([Date], "MM") & "/" & Date.ToText([Date], "yyyy")

Change [Date] to whatever your date column is called. Date.ToText converts a date time to text, which is then concatenated. You can then filter on that column. For issues like this it is best to have some sort of calendar table.

1
votes

You can create a new column in using query editor in power bi:

mon_year = left(<column_name>, 3) & Right(<column_name>, 4)

Note: Make sure your are connected to dataset in import mode because in live connection you will not be able to create New Column in Power BI.