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.
We are trying to look at order behaviour versus stocking policy.
01-Sep-2017 to 31-12-2017 SKU 1 is available in warehouse X & Y
01-Jan-2018 to 15-Mar-2018 SKU 1 is available in warehouse X
16-Mar-2018 to 17-Jun-2018 SKU 1 is available in warehouse Y
To enable us to see how the customer ordering behaviour is affecting our ability to ship to the customer. I would like to pull the available warehouse through to each of my orders.
The order would contain an order date and SKU number.
How do I relate the warehouse back to the order?
Solved! Go to Solution.
Hi @SJ,
Based on my understanding, you have two tables:
DateRange table: SKU, StartDate, EndDate, WareHouse
Order table: SKU, OrderDate
You could create a calculated table with this formula:
Result Tb = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Date Range', Orders ), 'Date Range'[SKU] = Orders[Order SKU] && Orders[OrderDate] >= 'Date Range'[StartDate] && Orders[OrderDate] <= 'Date Range'[EndDate] ), "SKU", Orders[Order SKU], "Date", Orders[OrderDate], "warehouse", 'Date Range'[warehouse] )
Best regards,
Yuliana Gu
Hi Yuliana,
I found the logic by looking at some of you had provided in your initial suggestion above and then researching online:
Production Location = CALCULATE(FIRSTNONBLANK('Direct SKUs'[Production Location],1),FILTER(ALL('Direct SKUs'),'Direct SKUs'[Material]='SAP Delivery Data'[Material] && 'Direct SKUs'[Date From]<='SAP Delivery Data'[Deliv.Date] && 'Direct SKUs'[Date To]>'SAP Delivery Data'[Deliv.Date]))
So although your solution was not what i was looking it helped me to the right one.
Thanks for your support.
Soren
Hi @SJ,
Based on my understanding, you have two tables:
DateRange table: SKU, StartDate, EndDate, WareHouse
Order table: SKU, OrderDate
You could create a calculated table with this formula:
Result Tb = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Date Range', Orders ), 'Date Range'[SKU] = Orders[Order SKU] && Orders[OrderDate] >= 'Date Range'[StartDate] && Orders[OrderDate] <= 'Date Range'[EndDate] ), "SKU", Orders[Order SKU], "Date", Orders[OrderDate], "warehouse", 'Date Range'[warehouse] )
Best regards,
Yuliana Gu
Hi Yuliana,
Thanks for your reply and sorry for my delay in trying this out (Was given other priorities to deal with first).
I have now tried your proposal but it doesn't necesarily answer my question.
Your solution creates a further table which I am not interested in.
All I want to do is showing the Warehouse on the Orders table.
Do you havea solution to this?
Regards,
Soren
Hi Yuliana,
I found the logic by looking at some of you had provided in your initial suggestion above and then researching online:
Production Location = CALCULATE(FIRSTNONBLANK('Direct SKUs'[Production Location],1),FILTER(ALL('Direct SKUs'),'Direct SKUs'[Material]='SAP Delivery Data'[Material] && 'Direct SKUs'[Date From]<='SAP Delivery Data'[Deliv.Date] && 'Direct SKUs'[Date To]>'SAP Delivery Data'[Deliv.Date]))
So although your solution was not what i was looking it helped me to the right one.
Thanks for your support.
Soren
Thanks I will give this a go.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |