Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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!
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |