Lookup a Date from a Date Table

[nadirS]

I have two tables in Power BI Desktop - One is a date Table (Attached) and another one which has the Start Date and Time for an activity. The completion time for all tasks is 5 days. The date table has a column (Holidays) which gives me 0's for Weekends/Holidays and 1's for Working days. I want to be able to Calculate the 'Proposed Completion Date'.

So Basically taking the SLAStartwithHolidays Date from One table adding 5 days to it and providing the corresponding date from the date table.

For Example if the Date from SLAStartwihHolidays is 01/02/2020 adding 5 days (Sum of 1's from the Holidays Colum) should give me 1/08/2020. 

I cannot just add 5 days to the start date becuse that will not take into account work days / holidays. 

 Date Table: 

Data Table has the following Field: 

[v-easonf-msft · Accepted solution]

Hi, @nadirS 

Try to  create calculated column as below:

Rownumber =
VAR tab =
    FILTER ( ALL ( 'Table' ), 'Table'[Holiday] = 1 )
RETURN
    IF (
        [Holiday] = 1,
        COUNTX ( FILTER ( tab, [Date] <= EARLIER ( 'Table'[Date] ) ), [Holiday] )
    )

Flag = [Rownumber] + 4

Result = 
var _enddate=MAXX(ALL('Table'),'Table'[Date])-5
return
IF (
    'Table'[Holiday] = 1 &&'Table'[Date]<=_enddate,
    LOOKUPVALUE ( 'Table'[Date], 'Table'[Rownumber], 'Table'[Flag], [Date] ),
    BLANK()
)

 The result will show as below:

Please check attached pbbix for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nadirS]

@Greg_Deckler @amitchandak