Show different date dimension on two axis

[aemb]

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

[parry2k]

@aemb 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.

[vickyArce · Accepted solution]

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.

[aemb]

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]

@aemb 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.

[aemb]

I see it's a bit confusing @parry2k  ... in this concrete example there's one order placed on 10th of January, which is allocated to contracts from dec/jan/feb/mar.

I would like to see the order number and the order date (10th of Jan) in one row as headers, and the contract allocation in the column headers.

All the dates are in the whacky format so I can't just add the column from the source table.. 

Does that clarify?