Forum Discussion
Mark as date table - How to
I did some more investigation on this:
OPEN_ORDERS table starts from: 2006-04-26 to: 2019-09-04
DATES table starts from: 2006-04-26 to: 2019-09-12
Filtering DATES[date] to start from 2006-04-27 -> OPEN_ORDERS[date] starts is from: 2006-04-27 to: 2017-03-03
Filtering DATES[date] start from 2006-04-26 -> OPEN_ORDERS[date] is from 2006-04-26 to: 2019-09-04
Looks like the first DATES[date] is curropted beacuse when I filter on it, all OPEN_ORDERS[date] after 2017-03-03 appear
Does anyone encountered a DATE table with Date field which the first date value curropted his data?
Managed to solve the issue, though now I have a new one...
The problem was, that the source query send a datetime field.
On the Power BI query editor, when I changed it to date field, for some reason the timestamp still affected the relationships between tables.
I had to cast the fields from the source SQL query in order to get only the date.
The tables are quite simple.
Open_Dates: [Order_id<text>, open_date<date>]
Close_Dates: [Order_id<text>, close_date<date>]
Date_Table: [date<date>]
The relations are on the date fields: {1:1)
Open_Dates[open_date] <-> Date_Table[date]
Close_Dates[open_date] <-> Date_Table[date]