Month and Year Filters for Dates with Data from SQL database

[emilyhch]

Hi there! 

I am trying to create a month and a year filter for my dashboard. However, based on the research my team have done, it looks like if the data is linked with a SQL database, we are unable to modify some of the setting to make Power BI see the dates as how it would see it when the data is linked through excel.

Current situation: My date data shows "date" (screenshot 1) but the little calendar icon doesn't showing up in the Report View(screenshot 2) and I cannot create the hierchay to create the month and year filters. Attaching a couple screenshots to explain the situation. 

screenshot 1

screenshot 2

screenshot 3 (what it should look like) 

My understanding is if that calendar icon is present (screenshot 3), I would be able to use the hiarchay to create the month and year filter but I couldn't find a way to make that icon show up. What can I do to create these filters for my data? I also need it do see it as date because I want to calculate the days between the created date and completed date.

Thank you very much! 

Emily 

[vicky_ · Accepted solution]

Please check if you have a M:1 relationship between the createdDate and another date column (especially with a date / calendar table). If you have that relationship, then you need to use the Date Heirarchy in the 1 side (i.e the date table). You can read: https://community.fabric.microsoft.com/t5/Community-Blog/Why-there-is-no-date-hierarchy/ba-p/2266682 for more details

If you don't want to use that date table (for whatever reason), you could add column(s) to your table to grab the year / month to use in your slicer. But that is not best practice.