Forum Discussion
Show different date dimension on two axis
Hi all,
I've tried finding a similar topic but to no avail so far - hopefully you can help!
I have a table of Orders and a table with Contracts. The dates in the tables are in an Oracle format, and need converting to a date dimension through a conversion table - so far so good. However I would like to show a list of the Orders by Actual Ship date (active relationship Actual ship date - Date to convert), and see the Contract months in the columns (inactive relationship Contract Month - Date to convert).
Simplified example;
Output should look like this (converted actual ship date but also converted contract month dates)
| Contract month | ||||
| Order number | Promised ship date | feb-19 | mrt-19 | apr-19 |
| 123123 | 8-4-2019 | 100 | ||
| 456456 | 3-4-2019 | 200 | ||
| 789789 | 1-4-2019 | 300 |
I cannot get this to work and show the contract months correctly (tried to work with DAX, add columns etc) but not sure what the best way is (and didn't get any result so far)...
I would much appreciate your input! Thanks a lot in advance.
the simplified sample data:
| Order Table | |||||
| Order number | Quantity | Contract | Promised Ship date | Actual Ship Date | |
| 123123 | 100 | 789 | 119098 | 119099 | |
| 456456 | 200 | 987 | 119093 | 119093 | |
| 789789 | 300 | 987 | 119091 | 119093 |
| Contract_Table | |
| Contract number | Contract month |
| 789 | 119091 |
| 987 | 119091 |
| Conversion_Date | ||
| Date to convert | Date | |
| 119091 | 1-4-2019 | |
| 119092 | 2-4-2019 | |
| 119093 | 3-4-2019 | |
| 119094 | 4-4-2019 | |
| 119095 | 5-4-2019 | |
| 119096 | 6-4-2019 | |
| 119097 | 7-4-2019 | |
| 119098 | 8-4-2019 | |
| 119099 | 9-4-2019 |
| Date_Dimension | ||||
| Date | Year | Month | etc | |
| 1-4-2019 | 2019 | Apr | ||
| 2-4-2019 | 2019 | Apr | ||
| 3-4-2019 | 2019 | Apr | ||
| 4-4-2019 | 2019 | Apr | ||
| 5-4-2019 | 2019 | Apr | ||
| 6-4-2019 | 2019 | Apr | ||
| 7-4-2019 | 2019 | Apr | ||
| 8-4-2019 | 2019 | Apr | ||
| 9-4-2019 | 2019 | Apr |
What you can do is:
1. In order table, merge to Conversion_Date table to get the new PromisedShipDate.Date, which is a DateTime format column;Merge to Conversion_Date table again to get the new ActualShipDate.Date
2. add columns in the Order table with Name : [Feb-19], [Mrt-19],[Apr-19] etc, the Power Query M language script will be like = if Date.Month([ActualShipDate.Date]) = 2 then [Quantity] else 0.
3. create a table in report view with columns you need.
5 Replies
- parry2k
Super User
Anonymous you can use something like this, basically userelationship will use inactive relations as active.
Qty = CALCULATE( SUM( 'Order'[Quantity] ), USERELATIONSHIP( Contract[Contract number], 'Order'[Contract] ), USERELATIONSHIP( Contract[Contract month], 'Converted Date'[Date to convert] ) )Also not sure if keeping date_dimension in model has any value, since you already have converted date dimension, you should keep that and remove the other one.
What you can do is:
1. In order table, merge to Conversion_Date table to get the new PromisedShipDate.Date, which is a DateTime format column;Merge to Conversion_Date table again to get the new ActualShipDate.Date
2. add columns in the Order table with Name : [Feb-19], [Mrt-19],[Apr-19] etc, the Power Query M language script will be like = if Date.Month([ActualShipDate.Date]) = 2 then [Quantity] else 0.
3. create a table in report view with columns you need.
Thanks for thinking along parry2k
I have two measures that give the shipped quantity by contract month and also by ship date - that works.
however I would like to see the X-axis show the contract monht, and the Y-axis show the shipment date... that's not working with the measures..
What this gives:
What I'm looking for is this:
(the blank contract month is dec16 which is excluded from the data).
ps. I've merged the two date dimension tables (the second one had additional information) - indeed it was overcomplicating the model - thanks!
- parry2k
Anonymous i'm not fully sue what you mean, when the quantity is blank it is not going to show the month, may be i'm missing something here.
