Slicer with filtering visuals with 2 different date columns

[Nudo]

Hey

Pretty new to Powerbi. I've created a report and need to filter a visual based on 2 created dates on 2 different tables with a dropdown slicer. I've created a date table and made a relationship but it's still not working.

I think I have a couple of issues and I'm having trouble resolving them,
1. Our dates in these columns are done with timestamps so the dates in my date table aren't picking them up (not sure if this is what's happening but that's my guess)
2. I want to be able to filter my date table date column into heiracy or years, qtrs and months. But if I create a heiracy on my date table it doesn't give me the options of choosing those like other date columns I use. 

I've spent many hours on google trying to resolve this issue, any help will be greatly appreciated

Thanks

[v-jingzhang · Accepted solution]

Hi @Nudo 

For the first issue, your guess is correct. So in those two tables, add a Date column to have only date values from Created Date column. Change data type of the new column to Date instead of Datetime to remove timestamps. 

In Date table, also change the data type of Date column to Date. Then create relationships on Date columns. In a slicer, you can drag Year, Quarter, Month columns to it at the same time to make a hierarchy slicer. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Nudo , You should create a date in both tables without time stamp and join that with Date table. In Date table you can have month Qtr, Year etc

Example new column

Date = datevalue([Craeted Date])

calendar table

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[Nudo]

Sorry I have no idea how to use that code, it's telling me everything from calendar and after is incorrect format.