calculate date on baose of working days and lead-time

[FranzMei]

Hello all, i hope you can help me with the following question:
i have two tables, trying to create a measure or column:

1. calendar: with working days, means that I have a column in my calendar which gives a “1”, if it is a working day
2. order table: it contains the date on which we created the order and a leadtime for the product in working days

my question is e.g. if i place the order on 15th of march [order-date] and the [lead time] e.g. 12 working-days, on which date should i receive my product?

I hope my question is clear, thanks a lot for your help in advance, please find sample file with link

link to sample

kind regards, thanks in advance

Franz 

[dax · Accepted solution]

Hi FranzMei,

You coud create a climn like below in calendar

Column =
VAR temp =
    CALCULATE (
        SUM ( calenader[working day] ),
        FILTER ( ALL ( calenader ), calenader[date] <= EARLIER ( calenader[date] ) )
    )
RETURN
    IF ( calenader[working day] = 0, 0, temp )

 Then create measure like below

Measure 5 =
LOOKUPVALUE (
    calenader[date],
    calenader[Column], MIN ( 'Table'[lead-time] ) - 1
        + LOOKUPVALUE ( calenader[Column], calenader[date], MIN ( 'Table'[order date] ) )
)

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FranzMei]

Thank you very much, exactly what i need, works perfect

kind regards

franz