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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi yanivshe 

Create relationship as below

If there are many duplicated dates in one table, please use "many to one" relationship

 

Then create measures in date table

the ammount of open orders = COUNT('open'[order id])

the ammount of close orders = COUNT('close'[order id])

diff = [the ammount of open orders]-[the ammount of close orders]

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • yanivshe's avatar
    yanivshe
    Helper I
    6 years ago

    Hi,

     

    Sorry for the late reply.

     

    I tried your suggestion and it sounds good.

    The problem is that when I put the measures in a table not all dates appear :smileyfrustrated:


    Here is the model for "OPEN_DATES"


    Here is the model for "Dates" table: (marked as date table)
    Here is the relationship: (on the dates fields)

    In my first screenshot you can see that Many of the dates are blank.

    Any idea what is wrong here?

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi yanivshe 

      I can't reproduce your problem.

      Could you share a simple file(remove large data, just keep simple data what can show the problem)?

       

      Best Regards
      Maggie

      • 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.