Hello all, this is my first topic i please here, hope you can help me with the following question:
i have two tables, trying to create a measure:
calendar: with working days, means that I have a column in my calendar which gives a “1”, if it is a working day
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, kind regards
@Franz,
Take a look at the attached file. But I broke this down into a few measures, but basically I filtered the date table from the order date till order date + lead time. I was then able to get the count of non-working days. Using those two bits of info I was able to get to the final result. If you put all the measures on the table it could help you see it if you are having trouble, but there is no need for all those intermediate measure to be on the table. you could actually build this into one formula, but maybe these measures could be used elsewhere. sampledate_order.pbv2.pbix (116.1 KB)
Order Date Plus Lead Time =
DATEADD('table calendar'[Date],[Total Days Lead Time] -1,DAY)
Total Working Days =
CALCULATE(
COUNTROWS( 'table calendar'),
DATESBETWEEN( 'table calendar'[Date], MAX('table order'[date of order]),[Order Date Plus Lead Time]),
'table calendar'[working day] =1,
ALL( 'table order')
)
Total NonWorking Days =
CALCULATE(
COUNTROWS( 'table calendar'),
DATESBETWEEN( 'table calendar'[Date], MAX('table order'[date of order]),[Order Date Plus Lead Time]),
'table calendar'[working day] =0,
ALL( 'table order')
)
Total Day Offset = [Total Days Lead Time] + [Total NonWorking Days]
Final Desired Result =
IF(
NOT(
ISBLANK( [Total Days Lead Time])),
DATEADD(
'table calendar'[Date],
[Total Day Offset]-1,
DAY)
)
