I want to create a Calculated Column to find next Business Days

[vrushabjain510]

I have a column which says whether it is a Business Day or Non Business Day

I have labelled Days of the week as follows:

Sun =1

Mon =2

Tues = 3

Wed = 4

Thur = 5

Fri = 6

Sat = 7

I need to Calculate below colums using Approved PO Date as shown in Table Below

For Eg. Selection of Bidders in table below should be after 1 Business Date from Approved PO Date

Technical Evaluation should be after 2 Business Days from Selection of Bidders Date and so on..

Note: If any date comes on a Non Business Date then it should consider the next Business Date

My Business Days are from Sunday to Thursday and Non Business Days are Friday and Saturday

PO Number	Approved PO Date	Selection of Bidders	Technical Evaluation	Bidder Response Time	DOF Approval
PO-2021-006	29/06/2021	Approved PO Date +1 Business Day	Selection of Bidders + 2 Business Days	Technical Evaluation + 10 Business Days	Bidders Response Time + 4 Business Days

Can anyone help me regarding this?

Thanks in Advance!

[v-henryk-mstf]

Hi @vrushabjain510 ,

For the information you gave, refer to the screenshot below, do I understand what you mean correctly? Find the corresponding business day in each column according to Approve PO date.

If not, please further clarify your needs, provide corresponding test data or screenshots of expected results, I will answer you as soon as possible.

Best Regards,
Henry

[vrushabjain510]

Yes,  the screenshot is exactly what I am looking for

[v-henryk-mstf]

Hi @vrushabjain510 ,

Do you want to create the next four columns?

Best Regards,
Henry

[vrushabjain510]

Hi Henry,

Yes I need to create next four calcuated columns

Thanks & Regards,

Vrushab 

[v-henryk-mstf · Accepted solution]

Hi @vrushabjain510 ,

I did a test and need to create a calendar from Sunday to Thursday. Then according to the approve po data to obtain the corresponding business day, refer to the following:

IsWorkday = IF(WEEKDAY([Date],2)>5,0,1)

Sum = 
VAR _sum =
    SUMX (
        FILTER ( ALL ( 'Calendar' ), [Date] <= EARLIER ( 'Calendar'[Date] ) ),
        [IsWorkday]
    )
RETURN
    IF ( [IsWorkday] = 0, BLANK (), _sum )

PO Date + 1 = 
var _sum=RELATED('Calendar'[Sum])
return
CALCULATE(MAX('Calendar'[Date]),FILTER(ALL('Calendar'),[Sum]=_sum+1))

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Below is the sample pbix file.

[vrushabjain510]

Hi Henry,

Thanks for the solution. This works perfectly

Appreciate your time and efforts!

[vrushabjain510]

Thanks Henry.

I will test it and let you know soon. 

Cheers!

[v-henryk-mstf]

Hi @vrushabjain510 ,

If the problem has been solved, you can mark the correct response as a standard answer to help the other members find it more quickly.😊

Best Regards,
Henry