Calculate the due date based on start date + working days

[EricBI90]

Hello Community!,

I am relative new to Power BI and I'm trying to create something in my power bi report, but I can't seem to get it work. I hope someone can help me with this.

The goal is to calculate the ‘DueDate’ by calculating ‘Arrived_Date_Time’ (the startdate) + ‘DAYSTOSTOCK’ (these days are workdays!).

In my screenshot attached,

• the colums ‘Arrived_Date_Time’ and ‘DAYSTOSTOCK’ are from ‘Table A’,
• the columns ‘'Nextworkday1BE, 'Nextworkday2BE, 'Nextworkday3BE' are from ‘Table B’ (this is a calendar table).
• Tables A and B have a relation based on unique key ‘Date’.
• Data connections are all ‘Direct Queries’ because it has to be a 'real-time' report.

So what I want to achieve is a new single column with the calculated due date.

For example:

On 05/01/2023 stock arrived. It should be processed within 3 business days, so the due date should be 10/01/2023 ('Nextworkday3BE').
Already tried with an IF and a SWITCH (a.workday = 1, then b.Nextworkday1BE, a.workday = 2, then b.Nextworkday2BE, a.workday = 3 then b.Nextworkday3BE) but can't get it to work.

Can anyone advise me in this / have any tips?
Much appreciated!

[tamerj1]

Hi @EricBI90 

please try

DueDate =
MAXX (
TableA,
SWITCH (
TableA[DAYSTOSTOCK],
1, RELATED ( TableB[Nextworkday1BE] ),
2, RELATED ( TableB[Nextworkday2BE] ),
3, RELATED ( TableB[Nextworkday3BE] )
) + TableA[Arrived_Date_Time]
)

[EricBI90]

Hi @tamerj1 
Thank you for your suggestion! Your DAX seems to be OK but I get the following error message:
"The column 'DML DimDate[NextWorkday1BE]' either doesn't exist or doesn't have a relationship to any table available in the current context."

As you can see in the screenshots below, their is an relationship between the tables. And both fields are 'Date' types.
Any ideas? 
(fyi,
table 'A' = VW_SLA_CALCULATIES, from database A
table 'B' = DimDate, from database B. Can it have something to do with that the tables are from two different sources?)

[tamerj1]

@EricBI90 

Apparently this a week relationship. The tables are from different sources. One is direct import and the other is direct query. Let me try to think of other way to do it.