Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
FranzMei
Helper I
Helper I

calculate date on baose of working days and lead-time

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

 

 

working days.png

 

link to sample

 

kind regards, thanks in advance

 

Franz 

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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 )

132.PNG

 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] ) )
)

133.PNG

 

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.

View solution in original post

2 REPLIES 2
dax
Community Support
Community Support

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 )

132.PNG

 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] ) )
)

133.PNG

 

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.

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

 

kind regards

franz

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.