Problems merging tables on date as key.

[Anonymous]

Hi,

I am trying to change calendar dates in my report to financial.

My method to do this was to create a new table then match on to the date Key.

Date =

　　GENERATE (

　　　　CALENDAR ( DATE ( 2014, 1, 1 ), DATE ( 2021, 12, 31 ) ),

　　　　VAR currentDay = [Date]

　　　 VAR day = DAY( currentDay )

　　　 VAR month =　 MONTH ( currentDay )

　　　　VAR year =　 YEAR ( currentDay )

VAR FiscalQuarter = IF(month=1,"4",IF(month=2,"4",IF(month=3,"4",IF(month=4,"1",IF(month=5,"1",IF(month=6,"1",IF(month=7,"2",IF(month=8,"2",IF(month=9,"2",IF(month=10,"3",IF(month=11,"3",IF(month=12,"3"))))))))))))

VAR FY = IF(month<=3,"FY"&RIGHT(year-1,2),"FY"&RIGHT(year,2))

　 RETURN　　 ROW (

　　　　"day", day,

　　　　"month", month,

　　　　"year", year,

"quarter", FiscalQuarter,

"fy", FY ))

My other table has the key as dd/mm/yyyy format.

The tables are not matching. I merged them on date as 1 to 1.

Is it because the new table includes a time stamp of 00:00:00?

If so, I changed this in the modelling format ribbon but it still does not merge when I try to create a matrix, using the new financial dates from the new table.

Thanks

[Anonymous · Accepted solution]

I solvd this.

I had to change the formats in the modelling to both the exact same.

Strangely it changed the date to a text field in the column not created by DAX