Adding only workdays to a specific date

[Anonymous]

Hello

I have a data column

i have a number of days column

these days can wary from 1-50 days

What i need help figuring out, is how do i subract those days in "workdays" from that date and then land on a non workday.

The date is delivery date + how many transport days.

So what i need is the delivery date without the transport days so i can match it with another date

So example

i whanto subtract lets say 50 work days from the date 01.11.2017

it will work to just write it like so column = [date] - [days]

but i need it to only subtract workdays, and the date haseto land on a workday aswell

Any help would be welcomed

-Robin

[sdjensen · Accepted solution]

Hi,

I created the calculations.

Try having a look at this: https://www.dropbox.com/s/5fareeag77bfc6x/power%20bi%20example.pbix?dl=0&m=

Result:

[v-chuncz-msft]

@Anonymous,

Based on my experience, you may take a look at TOPN Function.

[Anonymous]

If you have the change, could you explane alittle more whet you mean?

I dont understand how to use that in this case

[v-chuncz-msft]

@Anonymous,

Share us a simplified model please.

[Anonymous]

Sure thing

A couple of dates as an example. We need to add these days to the date, but make sure that only the work days gets added, skip saturday and sunday

[Anonymous]

@v-chuncz-msft

As an example to further explane what i mean

the date 02.04.2017 with 16 days transport time would need the result to be 24.04.2017 when just counting work days.

and NOT  18.04.2017 (countet with weekends)

[sdjensen]

Try adding a working days index to your period table. If a day is not a working day then assign the same index number to this day as the previous day - then you should be able to calculate a column in your table where you find the index of your initial date add the transportation days to the index and return the lowest date with that index.

[Anonymous]

@sdjensen

thank you for the reply

Could you give me an example on how you would write that? i am still very new to writing my own columns/measures

[sdjensen]

This would be much easier if you shared a base model with some date - at least the data you posted earlier (as a picture) and a period table.

But to calculate the index in your period table you could try something like this.

Column1:
IsWorkingDay = IF( Period[DayOfWeekNumber] = 6 || Period[DayOfWeekNumber] = 7; 0; 1 ) 

Column2:
WorkingDaysIndex = 
RANKX(
    FILTER(
        Period;
        Period[IsWorkingDay] = 1
    );
    Period[Date];
    ;ASC
) 

IsWorkingDay calculates a column that will return a 1 if the day is not a Saturday or Sunday.

WorkingDaysIndex then calculates the index based on IsWorkingDay.

I can see from my small test that the rank on a Saturday is 1 larger than on a Friday, so instead of MIN date you should find the MAX date where index is equal Index from starting date + Transportation days

[Anonymous]

@sdjensen

Made a quick model here for you, hope that helps. 

https://www.dropbox.com/s/829noofd84yrr38/power%20bi%20example.pbix?dl=0

[sdjensen · Accepted solution]

Hi,

I created the calculations.

Try having a look at this: https://www.dropbox.com/s/5fareeag77bfc6x/power%20bi%20example.pbix?dl=0&m=

Result:

[Anonymous]

Hi! I have this problem too, but there are specific # of days to add depending on the country's requirement. How to add # of days per country? 

[sdjensen]

Which results is wrong?

In an earlier reply you stated that the result should be 25-04-2017 if the date is 20-04-2017 and transportations days is 16 and that is what I get from my result.

[Anonymous]

@sdjensen

I see now whats wrong. the dates i gave you in the example were almost all weekends starter dates. So when i count from the first monday it turns out wrong. But the one from wednesday (onsdag) 5.july is correct.

Sorry the fault is all on my side.

Thank you very mutch for youre help!

[sdjensen]

Could you give me the correct results for the demo data then I can have a look at it, but it's easier if I have the wanted result. I have an idea about that is wrong with the calculation, but it's nice to have something to confirm that I make the correct changes.

[Anonymous]

@sdjensen

Hey and thank you for the reply

There seems to be something wrong with the calc. When i count the days on the calander it dosent match.

I dont know whats wrong

[v-chuncz-msft]

@Anonymous,

You may refer to the following DAX that adds a calculated column.

Column =
VAR d = Table1[Date]
RETURN
    MAXX (
        FILTER (
            'Calendar',
            VAR d2 = 'Calendar'[Date] RETURN 'Calendar'[IsWorkday] = 1
                && CALCULATE (
                    COUNTROWS ( 'Calendar' ),
                    ALL ( 'Calendar' ),
                    DATESBETWEEN ( 'Calendar'[Date], d + 1, d2 ),
                    'Calendar'[IsWorkday] = 1
                )
                    = VALUE ( Table1[Transport days] )
        ),
        'Calendar'[Date]
    )

[Anonymous]

@v-chuncz-msft 

Hello again, thank you for youre reply

I am running into this issue, any surgestions on how to fix this?

[v-chuncz-msft]

@Anonymous,

Use the method from @sdjensen to obtain good performance.

@sdjensen,

The result might need to be shown as below.

[Anonymous]

@sdjensen and @sdjensen

Thank you very mutch for youre help!

@sdjensen youre solution works perfectly now. Thank you very mutch again

This just made my life alot easier 😉

[sdjensen]

@Anonymous - you are welcome, but I didn't make any changes? So how do you all of a sudden get the right dates?