Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Adding only workdays to a specific date

‎11-03-2017 02:28 AM

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

Labels:
Message 1 of 27
8,178 Views
0
2 ACCEPTED SOLUTIONS
sdjensen
Solution Sage
Solution Sage
In response to Anonymous

‎11-09-2017 02:37 AM

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:

WorkingDaysTransportDays.png

 

/sdjensen

View solution in original post

Message 22 of 27
9,062 Views
0
sdjensen
Solution Sage
Solution Sage
In response to Anonymous

‎11-10-2017 04:10 AM

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
    )
)
/sdjensen

View solution in original post

Message 20 of 27
8,544 Views
0
26 REPLIES 26

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All