Forum Discussion
Adding only workdays to a specific date
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 ) )
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.
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
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
Made a quick model here for you, hope that helps.
https://www.dropbox.com/s/829noofd84yrr38/power%20bi%20example.pbix?dl=0
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] )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:
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
Hello again, thank you for youre reply
I am running into this issue, any surgestions on how to fix this?
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.
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!
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 - you are welcome, but I didn't make any changes? So how do you all of a sudden get the right dates?
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! 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?
