Calculate a new date additioning date+Working days

[Stéphane]

Hi all,

I need your knowledge to solve following question:

I have 2 tables:

- Sales order, with Created date

- Calendar table, from 2015 to 2025, with an additionnal column Working days (0 or 1 value)

I am trying to calculate in a new column a Sales order Treatment limit date, so that Treatment limit date = Created date + 3 working days.

Said differently: CALCULATE(SUM([Calendar date]);DATESBETWEEN([Calendar Working days];Sales order Created date;Treatment limit date)) = 3

Any idea?

I guess this is turning around DATEADD, CALCULATE,... but I don't get it 🙂

Thank you for your help,

Stéphane

[Mariusz · Accepted solution]

Hi @Stéphane 

Try something like 

Column = 
VAR __date = 'Sales order'[Date]
VAR __tlb = FILTER( 'Calendar', 'Calendar'[Date] > __date &&  'Calendar'[Is Weekend] = FALSE() )
RETURN 
CALCULATE(
    MAX( 'Calendar'[Date] ),
    TOPN( 3, __tlb, [Date], ASC )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Stéphane]

It works perfectly, thanks a lot!

[amitchandak]

Refer : https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/td-p/146945

https://community.powerbi.com/t5/Desktop/How-to-add-time-to-working-days-with-its-working-hours-excl...

[Stéphane]

Hi, thanks for your help!

First link is a smart way to solve the issue in a different way, I'll keep it it mind and use it if I don't manage to get something from the second one.

I'll keep you posted.

[Greg_Deckler]

Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I can't tell 100% exactly what you want, but these might help: See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

[Stéphane]

Thank you for your answer, here are some examples, unfortunately the links provided don't help it seems to me:

Sales order table:

Calendar table: