Forum Discussion

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
6 years ago
Solved

Weird behavior with DATEADD! Please HELP!

Hey PBI Gurus,   I'm running into a really weird issue with the DATEADD DAX function in Power BI Desktop. I've used this same calculation in several reports with no issues, however, it's really giv...
Need Help
  • CoreyP's avatar
    CoreyP
    6 years ago

    Hey all,

     

    Just wanted to keep you in the loop. I've come up with a working solution! 

    Here's what I did:

    I created a 2nd Date Table ( Date2 ), with a relationship between 'Date2'[Date] and 'BAND'[DATE - ORD RCV], as my first date table has a relationship with 'BAND'[DATE - ORD CLOSE]. ( I think this was causing issues )

     

    Then I added this column to table 'BAND':

    NextIndex = 
VAR OrderReceivedDate = BANDB[DATE - ORD RCV]

VAR CurrentDay = CALCULATE ( MIN ( 'Date2'[Working Day Index] ), 'Date2'[Date] = OrderReceivedDate )

VAR NextWorkingDay = CALCULATE ( MIN ( 'Date2'[Working Day Index] ), 'Date2'[Date] > OrderReceivedDate )

RETURN

IF( ISBLANK( CurrentDay ) , NextWorkingDay , 
    IF( BANDB[Past Cutoff] = 1 , NextWorkingDay ,
        IF( AND( BANDB[Past Cutoff] = 0 , ISBLANK(CurrentDay) ) , NextWorkingDay ,
CurrentDay )
 ))

     

    And finally this column which converts the Index value into a date value:

    Order RCV Date = 
                    LOOKUPVALUE( Date2[Date] , Date2[Working Day Index] , BANDB[NextIndex] )

     

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
6 years ago

Hi d_,

 

Yes, I agree the logic in the VAR formula is not correct as I'm trying to edit it from usage in another calculated column for the purposes of this column, Order RCV Date.

 

I would absolutely use your proposed solution, however, I realize it doesn't take holidays into account, only weekends. While there are only a handful of holidays, I'd still like it to be 100%.

CoreyP's avatar
CoreyP
Icon for Solution Sage rankSolution Sage
6 years ago

Hey all,

 

Just wanted to keep you in the loop. I've come up with a working solution! 

Here's what I did:

I created a 2nd Date Table ( Date2 ), with a relationship between 'Date2'[Date] and 'BAND'[DATE - ORD RCV], as my first date table has a relationship with 'BAND'[DATE - ORD CLOSE]. ( I think this was causing issues )

 

Then I added this column to table 'BAND':

NextIndex = 
VAR OrderReceivedDate = BANDB[DATE - ORD RCV]

VAR CurrentDay = CALCULATE ( MIN ( 'Date2'[Working Day Index] ), 'Date2'[Date] = OrderReceivedDate )

VAR NextWorkingDay = CALCULATE ( MIN ( 'Date2'[Working Day Index] ), 'Date2'[Date] > OrderReceivedDate )

RETURN

IF( ISBLANK( CurrentDay ) , NextWorkingDay , 
    IF( BANDB[Past Cutoff] = 1 , NextWorkingDay ,
        IF( AND( BANDB[Past Cutoff] = 0 , ISBLANK(CurrentDay) ) , NextWorkingDay ,
CurrentDay )
 ))

 

And finally this column which converts the Index value into a date value:

Order RCV Date = 
                    LOOKUPVALUE( Date2[Date] , Date2[Working Day Index] , BANDB[NextIndex] )