Mark as date table - How to

[yanivshe]

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 open orders vs close orders per date.

So what I did was to create a third table, a Date_Table, and mark it as date table.

The Date_Table is quite simple and contains only dates columns.

I setup the relations between the Date_Table and Open_Date on the date column ((1:1 relationship)

And formatted the date to be only date value (no timestamp)

When I put the 2 fields in a table visual , it is empty 😞

I tried to remove the "mark as date table" but it didnt work as well...

I'm guessing that the solution for the Open_Orders_Table will be the solution for the Close_Orders_Table

Any one has an idea what I need to do?

[yanivshe · Accepted solution]

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?

[v-juanli-msft]

Hi @yanivshe 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

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]

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 

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]

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]

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 · Accepted solution]

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 · Accepted solution]

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]

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]

[RobbeVL]

Most likely the Datetypes dont match, and the relationship is not correctly defined.

[Ani1991]

Hi @yanivshe 

If you don't mind can you please share some sample data or screenshots of the same.

Thanks,

Ani