Forum Discussion
direct application of WORKDAY function like in excel
Anonymous wrote:
Dear Phil_Seamark
Thanks for the response.
In your syntax, i couldn't figure out where to key in my customized day to add for each row.
I have attached my sample data. Column C is my requirement. Could you please have a look?
Thanks in advance!
Anonymous
You can create an calendar table as below
dimdate = VAR onlyWorkdays = FILTER ( CALENDAR ( "2017-01-01", "2017-12-31" ), WEEKDAY ( [Date] ) <> 1 && WEEKDAY ( [Date] ) <> 7 ) RETURN ADDCOLUMNS ( onlyWorkdays, "Index", RANKX ( onlyWorkdays, [Date],, ASC, DENSE ) )Then connect your source table to the calendar table, create a measure as
exw date = VAR DateIndex = MAX ( dimdate[Index] ) VAR LeadTime = MAX ( 'Table'[Lead Time] ) RETURN MAXX ( FILTER ( ALL ( dimdate ), dimdate[Index] = DateIndex + LeadTime ), dimdate[Date] )See more details in the pbix file.
Hi Anonymous
This is one way to do it as a calculated column. Just replace Table3 with your own tablename
exw date = VAR myDate = ADDCOLUMNS(FILTER(CALENDAR(Table3[Order Date],TODAY()),WEEKDAY([Date],3)<5),"Days",1) VAR Cumulative = ADDCOLUMNS( myDate, "D", SUMX(filter(myDate,[Date]<EARLIER([Date])),[Days]) ) RETURN MINX(FILTER(Cumulative,[D]='Table3'[Lead Time]),[Date])
Microsoft EmployeeHi Anonymous
What Eric_Zhang has suggested works perfectly for a calculated measure.
This is the syntax you might use if you'd like to have the value as a calculated column
dimdate =
VAR onlyWorkdays =
FILTER (
CALENDAR ( "2017-01-01", "2017-12-31" ),
WEEKDAY ( [Date] , 2 ) < 6
)
RETURN
ADDCOLUMNS (
onlyWorkdays,
"Index", RANKX ( onlyWorkdays, [Date],, ASC, DENSE )
)Create a relationship between this and your existing table, then add this calculated column
EXW Date =
VAR DateIndex = RELATED('dimdate'[Index])
RETURN CALCULATE(
MAX('dimdate'[Date]),
FILTER(dimdate,dimdate[Index] = DateIndex + 'Table1'[Lead Time])
)Dear Phil,
I have a small doubt in our earlier discussion. You have helped me on how to add particular no. of days to a date and then find a new workday. so for example if we have column1 + day1 = column2, in our formula we never reference anywhere our column1, so how formula is detecting and computing? i got this doubt when i wanted to perform the same for another new column. Can help please?
- Phil_Seamark
Hi Anonymous
Looks like the forumla I gave you simply provides a WORKDAY number for every working day in the year from the start of the year.
Did you want to display the number of working days from another starting point?
Dear Phil_Seamark
Yes, I have a starting point state to which i need to add particular number of days to form new workday column.
as in my attached image, my starting point will be OrderDate and i have to add Lead time to form the result EXWdate.
Thanks!
- Phil_Seamark
Hi Anonymous
This is one way to do it as a calculated column. Just replace Table3 with your own tablename
exw date = VAR myDate = ADDCOLUMNS(FILTER(CALENDAR(Table3[Order Date],TODAY()),WEEKDAY([Date],3)<5),"Days",1) VAR Cumulative = ADDCOLUMNS( myDate, "D", SUMX(filter(myDate,[Date]<EARLIER([Date])),[Days]) ) RETURN MINX(FILTER(Cumulative,[D]='Table3'[Lead Time]),[Date])