Forum Discussion

frknklcsln's avatar
frknklcsln
Icon for Helper II rankHelper II
4 years ago
Solved

workday DAX in power bi

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("I...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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] )
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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