Display the max date before a specific deadline for 2 date tables

Question

Hi,&nbsp;I would like to know when shall we place a purchase order before the deadline in case suppliers are on holidays.&nbsp;&nbsp;Ex. if the Expected delivery date is 31.12.2020 (could be any date) then the start of the procurement process shall begin 50 days prior, on 11.11.2020. (that’s done) But we need to make sure we consider all the supplier vacation between Expected delivery date and procurement process start date (only start date).&nbsp;So lets say between 31.12.2020 and 11.11.2020 there are suppliers who have together around 14 days of holiday. Then from date 11.11.2020 we should calculate / deduct these 14 days to obtain 28.10.2020 new order date.&nbsp;Ex.&nbsp;Table 1:&nbsp;Column 1 - Expected date of del.&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column B Expected day of del. (Procurement) -50 days&nbsp;DAX date range&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column 1 – 50 days&nbsp;01.01.2020&nbsp;to&nbsp;31.12.2040&nbsp;Table 2: Supplier holidays example&nbsp;Column 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column 2 - Start Holiday&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column 3 - End HolidaySupplier A Holiday 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 03.04.2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 07.03.2020Supplier A Holiday 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11.05.2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.05.2020Supplier B Holiday 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 06.04.2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 08.04.2020Supplier B Holiday 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.09.2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25.09.2020Supplier B Holiday 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 07.11.2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.11.2020and so on&nbsp;Both tables could be related via date only.&nbsp;Many thanks

v-alq-msft · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;&nbsp;Based on your description, I created data to reproduce your scenario.Table1(a calculated table):&nbsp;Table 1 = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))&nbsp;&nbsp;&nbsp;Supplier holiday:&nbsp;Then you may create a calculated column and three measures as follows.&nbsp;calculated column:
Supplier = LEFT('Supplier holiday'[Supplier Holiday],10)

measures:
Holidays = 
var _currentdel = SELECTEDVALUE('Table 1'[Expected date of del.])
var _currentdel50 = SELECTEDVALUE('Table 1'[Procurement])

var _startdate = MAX('Supplier holiday'[Start Holiday])
var _enddate = MAX('Supplier holiday'[End Holiday])

return
SWITCH(
    TRUE(),
    OR(_startdate&gt;_currentdel,_enddate&lt;_currentdel50),0,
    AND(_startdate&lt;_currentdel50,_enddate&gt;_currentdel50),DATEDIFF(_currentdel50,_enddate,DAY),
    AND(_startdate&gt;=_currentdel50,_enddate&lt;=_currentdel),DATEDIFF(_startdate,_enddate,DAY),
    AND(_startdate&lt;_currentdel,_enddate&gt;_currentdel),DATEDIFF(_startdate,_currentdel,DAY),
    BLANK()
)

TotalsDays = 
var _currentsupplier = MAX('Supplier holiday'[Supplier])
return
SUMX(
    FILTER(
        ALLSELECTED('Supplier holiday'),
        'Supplier holiday'[Supplier] = _currentsupplier
    ),
    [Holidays]
)+50

Max Date = SELECTEDVALUE('Table 1'[Expected date of del.])-[TotalsDays]&nbsp;&nbsp;Finally, you may use 'Expected date of del.' from Table1 as the slicer and a table visual to display the result.&nbsp;Best RegardsAllan&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Display the max date before a specific deadline for 2 date tables

2 Replies

Related content

Specific matrix

Specific visuals wont display for specific people

Create a Merged Deadline and Service History Table

Help Building Deadlines for Single Items with Multiple IDs

Image display in Power bi report