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.
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
Solved! Go to 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:
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 ) )
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
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 ) )
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.
Hi
Do you have a list of days as non working days or you want to use Sat & Sun only as non working?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |