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:

[Anonymous]

I was working on a demo for you to check out, then i realised that i had set the relationship from calender to the table with the wrong date. So that made the calculation all wrong. When i linked it to the right on it worked like a charm!

Abit embarrassing but it happens

[sdjensen · Accepted solution]

Okay - to assist others with similar challenges I will just make a short description of the solution I created.

First make sure that there is a relationship between the table and the calendar table.

Then in the calendar table create a few columns to create an index for workingdays

IsWorkday = SWITCH( WEEKDAY('Calendar'[Date]); 1; 0; 7; 0;1 )

WorkingDayIndex = 
RANKX(
    FILTER(
        'Calendar';
        'Calendar'[IsWorkDay] = 1
    );
    'Calendar'[Date];
    ;ASC
) 

Then in the table that should hold the result I created this calculation that make a simple lookup in the calendar table using the index column created above.

NewDate = 
VAR DateIdx = CALCULATE( MAX( 'Calendar'[WorkingDayIndex] ); 'Table1'[Date] = RELATED('Calendar'[Date] ) )
VAR NewDateIdx = DateIdx + Table1[Transport days]

RETURN
CALCULATE(
    MAX('Calendar'[Date]);
    FILTER(
        ALL('Calendar');
        'Calendar'[WorkingDayIndex] = NewDateIdx 
            && 'Calendar'[IsWorkday] = 1
    )
)

[TSP_PowerBi]

@sdjensen 

3 years later and it's still relevant.

I am trying to do the same thing the original poster was doing, except I just want to add 3 days to a date and exclude weekends.

I created the IsWorkday Column.

I created the WorkingDayIndex however, I do not understand the RANKX need.

 When creating a measure for this, I am not able to add this Date field.

NewDate = 
VAR DateIdx = CALCULATE( MAX( 'Calendar'[WorkingDayIndex] ); 'Table1'[Date] = RELATED('Calendar'[Date] ) )
VAR NewDateIdx = DateIdx + Table1[Transport days]

  Any help would be greatly appreciated.

[dilumd]

Hi

Do you have a list of days as non working days or you want to use Sat & Sun only as non working?

[Anonymous]

I have a calendar table were i use this

WorkDay =
VAR WeekDayNum =
WEEKDAY ( 'Dato + measures'[Date], 2 )
RETURN
(
IF ( WeekDayNum = 6 || WeekDayNum = 7, FALSE (), TRUE () )
)

But im open to other surgestions

[dilumd]

Need your help @v-sihou-msft