Forum Discussion

yanivshe's avatar
yanivshe
Helper I
7 years ago
Solved

Mark as date table - How to

Hi,   I'm having trouble modeling my tables. I have 2 tables of orders: - Open_Orders_Table: [order_id, open_date] - Close_Orders_Table: [order_id, close_date]   I want to show the ammount of ...
Need Help
  • yanivshe's avatar
    yanivshe
    6 years ago

    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?

  • yanivshe's avatar
    yanivshe
    6 years ago

    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.

yanivshe's avatar
yanivshe
Helper I
6 years ago

I cant share any data since it related to my work.

Let me consult with someone from my organization and see if they can help me with this manner.

I thought someone from the community encounterd such problem like I have.

yanivshe's avatar
yanivshe
Helper I
6 years ago

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?

  • yanivshe's avatar
    yanivshe
    Helper I
    6 years ago

    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.