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,
May I know how to create WORKDAY function in powerBI with DAX function? I have no holidays to add. I have a column with dates and another column with numerics. I just wanted to get the result in new column with the workday by adding numeric to my existing column. Is there a way to do it directly without any additional tables please?
Solved! Go to Solution.
@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])
Hi @Anonymous
You could try adding this calculated column to your date table
WORKDAY = SWITCH( WEEKDAY([Date],1), -- Is Sunday -- 7,0, -- Is Saturday -- 1,0, -- Else -- 1 )
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 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
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?
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!
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])
Hi Phil - Thank you for the elegant solution! Is it possible to modify this to account for holidays as well?
Dear @Phil_Seamark
sorry, i was away from office and couldn't reply fast.
It works perfectly.
can you just help me to understand what the value [Date] picks up?
other portion of the formula, i understand.
Thanks!
Dear @Phil_Seamark,
Apologies for the late reply as i was out of town.
I have tried the solution that you have given and it works perfectly for a calculated column which is my need. I am still trying to decode the funtions and trying to learn the logic how it works. The DAX funtions are new to me.
I might have missed something in my earlier query. I wanted to check how public holidays can be avoided while doing the (Workday Date + Lead time calculation). For example, if I have two different sets of public holidays, one for Malaysia and one for Thailand, then during calculation, if my Row's data belongs to malaysia, the result must exclude Malaysia public holidays alone and if the row is for Thailand, then it must exclude Thailand holidays alone. Could you please advice?
PS: Right now in excel, one country's holidays are one in one column. So I have 3 to 4 columns, and while using WORKDAY funtion, I just map to respective column.
Thanks for your great support!
@Eric_Zhang thanks a lot for advicing the solution through Measure.
Aha, I get it. I wasn't sure what the WORKDAY function did, but your image helps. I will have a look at this tonight for you.
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 |