Filter out year and month from date/time table

[Anonymous]

Hi

I have a date/time table like this: ( as you can see not all dates are unique )

I need to make 2 slicer filters now: 1 for year and 1 for month so in dropdown I could pick year 2019 and another dropdown month "January" and it would show me all the data in January 2019. I tried to make a copy column of the date table and choose format 2001(yyyy) so it would only show year but in dropdown then of course I have thousands of 2020's. It shouldn't be a hard problem I think but haven't found any solutions in google yet.

PS. Just in case I want to mention that I'm working with livequery. Import is not an option

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think the reason that the Slicer displays thousands of the 2020s is that you do not create a hierarchy for the date column, you can follow these links to create a hierarchy for the date column and place [Year] and [Month] for the two Slicers you created.

https://www.youtube.com/watch?v=x6vXVJZ_eTY

https://spreadsheeto.com/power-bi-hierarchy/

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

I would encourage you to make a Date table and then a separate Time table, and split your fact table column into date and time columns to relate to the Date and Time tables.  This will significantly reduce # of rows/file size and will make analysis easier, more flexible, and faster.

Regards,

Pat