topic Re: Calculate the due date based on start date + working days in DAX Commands and Tips

Calculate the due date based on start date + working days

EricBI90 — Thu, 11 May 2023 09:49:09 GMT

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!

Re: Calculate the due date based on start date + working days

tamerj1 — Thu, 11 May 2023 13:00:03 GMT

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

Re: Calculate the due date based on start date + working days

EricBI90 — Thu, 11 May 2023 14:57:44 GMT

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

Re: Calculate the due date based on start date + working days

tamerj1 — Thu, 11 May 2023 15:20:23 GMT

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.