Add what if Parameter to Date column to add only work days in Visual Table

[Anonymous]

Hi Everyone,

I have created a what if parameter which I want to add to 'End Date' column dynamically . My end goal is to get the 'Expected Date'  that accounts  only working day increment i-e 'End Date' + working day from the parameter values.

For example if user choose the parameter =5, I want my table to change as per below: I already have a Calender Table with indicator 1, if it is woking day. I will appreciate your help to make this happen 🙂 TIA

End Date	Expected Date
Saturday, May 1, 2021	Friday, May 7, 2021
Sunday, May 2, 2021	Friday, May 7, 2021
Monday, May 3, 2021	Monday, May 10, 2021
Tuesday, May 4, 2021	Tuesday, May 11, 2021
Wednesday, May 5, 2021	Wednesday, May 12, 2021
Thursday, May 6, 2021	Thursday, May 13, 2021
Friday, May 7, 2021	Friday, May 14, 2021
Saturday, May 8, 2021	Friday, May 14, 2021
Sunday, May 9, 2021	Friday, May 14, 2021

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Try this. 

create the column:

Column = SUMX(FILTER(Calender,Calender[Date]<=EARLIER(Calender[Date])),Calender[IsWorkDay])

Create the measure:

Measure = CALCULATE(MIN(Calender[Date]),FILTER(ALL(Calender),Calender[Column] = MAX(Calender[Column])+5))

See sample file attached below.

Result:

Besides,

Best Regards,

Community Support Team _ Tang

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

[Anonymous]

Thanks @v-xiaotang , I appreciate your help and yes it is working for me. Yes May 31st is a stat  holoday so that's why I used 0 as indicator. Thanks for reviewing though. 

[Anonymous]

Here is the sample data

https://drive.google.com/file/d/1Hd8sMRQ5-Nq8nZqkaFjPAWcQD_i_JVO1/view?usp=sharing 

[Anonymous]

Hi CNENFRNL,

I appreciate your quick reponse, However, My workday indicator column "IsWorkDay" is sitting in a different Table named 'Calender'. Can I make this measure to work in this scenario as well. 

Basically I have two table, one I posted above and second is Calender table with "IsWorkDay" column.

thanks

[CNENFRNL]

Assume that all weekends and holidays are indicated 0 as below,