Creating a Month/Year relationship for use in a slicer

[adamsumm]

Hello, 

I have two tables that I am trying to create a relationship to.  One is my key table used as a reference table for the date.  I want the date to span multiple years (this example shows 2017 through 2018).  

My other table shows my incident numbers and the date which it was created on.  

I have tried creating a 1 to many relationship between the date column and the opened at column.  For some reason my slicer will not work.  Both columns are formated as the data type Date.  

[v-yulgu-msft · Accepted solution]

Hi @adamsumm,

In date table, create a [Year Month] column like this:

Year Month = DateTable[date_year]&" "&DateTable[date_num]

Also, in incident table, create a [Year Month] column:

Year_Month = YEAR(Incidenttable[Open at]) & " " & MONTH(Incidenttable[Open at])

Please remove the original relationship, re-create a one to many relationship based on [Year Month].

Result

Best regards,

Yuliana Gu

[Anonymous]

Check in Query Editor if these are originally coming in as DATE-TIME data values.

For some reason, changing the data type from DateTime to Date sometimes won't properly truncate.

Instead, go to the Transform tab in the Ribbon.

Date & Time Column section

Click Date

Date Only.

That will properly truncate the date.

Let us know if this works!