direct application of WORKDAY function like in excel

[Anonymous]

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?

[Eric_Zhang · Accepted 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.

[Phil_Seamark]

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
                    )

[Anonymous]

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!

[Eric_Zhang · Accepted 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.

[Phil_Seamark]

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])
                )

[Anonymous]

@Phil_Seamark 

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?

[Anonymous]

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 · Accepted solution]

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])

[Jerelle]

Hi Phil - Thank you for the elegant solution!  Is it possible to modify this to account for holidays as well?

[Anonymous]

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!

[Anonymous]

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. 

[Phil_Seamark]

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. 

[Anonymous]

@Phil_Seamark

Thanks a lot! My mistake, i haven't been clear in the earlier msg.

Will wait!