workday DAX in power bi

[frknklcsln]

Hi,

I have an Excel formula. I want to carry it to the PBI.

The Excel formula:

=IF("Estimated Loading Date">
WORKDAY("Insert Date",10),

WORKDAY("Estimated Loading Date",-5),WORKDAY("Insert Date",2))

All dates are in the same table.

'Table'[Estimated Loading Date]

'Table'[Insert Date]

Any advice will be appreciated.

[DanBT]

For all looking for a different way to solve this without needing the date table here is an example that would work: 

Workdays = 

VAR WorkingDays = [Days Workload Remaining]

VAR RemainingDays = IF(WEEKDAY(TODAY() + WorkingDays, 2) >= 7, 7 - WEEKDAY(TODAY() + WorkingDays, 2), 0) + WorkingDays

VAR RemainingWeeks = INT(RemainingDays / 5)

VAR DaysToAdd = MOD(RemainingDays, 5)

VAR DaysToAddAdjusted = IF(WEEKDAY(TODAY() + DaysToAdd, 2) >= 6, DaysToAdd + 2, DaysToAdd)

VAR NextDate = TODAY() + (RemainingWeeks * 7) + DaysToAddAdjusted

RETURN IF(WEEKDAY(NextDate, 2) < 6, NextDate, NextDate + (9 - WEEKDAY(NextDate, 2)))

[tamerj1 · Accepted solution]

Hi @frknklcsln 

please try

=
VAR CurrentEstimatedDate = 'Table'[Estimated Loading Date]
VAR CurrentInsertDate = 'Table'[Insert Date]
VAR PreviousEstimatedDate = CurrentEstimatedDate - 5
VAR NextInsertDate1 = CurrentInsertDate + 2
VAR NextInsertDate2 = CurrentInsertDate + 10
VAR EstimatedDates =
    FILTER (
        CALENDAR ( PreviousEstimatedDate, CurrentEstimatedDate ),
        NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
    )
VAR InsetDates1 =
    FILTER (
        CALENDAR ( CurrentInsertDate, NextInsertDate1 ),
        NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
    )
VAR InsetDates2 =
    FILTER (
        CALENDAR ( CurrentInsertDate, NextInsertDate2 ),
        NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
    )
RETURN
    IF (
        CurrentEstimatedDate > MAXX ( InsetDates2, [Date] ),
        MAXX ( CurrentEstimatedDate, [Date] ),
        MAXX ( NextInsertDate1, [Date] )
    )

[amitchandak]

@frknklcsln , if you need networkdays between two dates, you have function -networkdays

Find usages and alternative here

https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c

If you want to create a workday flag

Work Day = if(WEEKDAY([Date],2)>=6,0,1)