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.