Relating time range data to individual dates in dataset

[SJ]

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?

[v-yulgu-msft · Accepted 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

[v-yulgu-msft · Accepted 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

[SJ]

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

[SJ · Accepted solution]

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

[SJ]

Thanks I will give this a go.