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

Hey again,

 

So after further validation, it turns out I'm not getting the correct date when the order is NOT after cutoff, aka 'BAND'[Past Cutoff] = 0. Please check the attached image, specifically, the highlighted areas. This is the part of the code that needs to be adjusted. If I replace "NextIndex -1" with (fn)OrderReceivedDate, it returns blank values, but I want it to return the same date that's in the field 'BAND'[DATE - ORD RCV]. In other words, "if 'BAND'[Past Cutoff] = 1, return NextIndex ( which is the next non-blank sequential value of field Working Day Index in my Date table ) , otherwise return the same date value in [DATE - ORD RCV], in other words, don't change the date. 

 

HOWEVER, there is a stipulation that if the order was received on a NON-working day, aka [Working Day Index] = blank, then return the date value of the next non-blank working day index.

 

I really appreciate you guys taking the time to help me with this!

 


Can someone help me out with this part of it? Again, when an order is Past cutoff, this works. 

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
6 years ago

I have a feeling this has something to do with your data model. As Amy has mentioned the DAX DATEADD function requires a contiguous series of dates as it actually shifts the filter context within the column rather than doing pure date base math. So if you do DATEADD to shift a date forwards one day, but that new date is not a value that already exists in the specified date column you will actually get a blank returned.

 

One simple work around if you just need to add days instead of:

DATEDADD( BANDB[DATE - ORD RCV], 1, DAY)

is to to do:

BANDB[DATE - ORD RCV] + 1

This makes use of the fact that internally dates are stored as the number of days since 31 Dec 1899. This will allow you to do basic add days functionality on any date column regardless of whether it has a contiguous range of values or not.

 

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

    Thank you for the reply! Now I understand why it's returning blanks. Thank you!

     

    However, I've realized that will not work as it doesn't take into account holidays. What i really need is to have the VAR formula I posted above edited to work for this specific situation. But, I'm not super knowledgeable about the VAR formula...someone on here actually wrote it for me the first time.

     

    Are you able to help me edit this so that it works for my purposes? I really appreciate you taking the time to help me with this.

    Thank you!

    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      6 years ago

      I don't think the logic in the VAR formula is correct. It's actually shifting weekend dates backwards. I think if you altered your original formula with the nested IF statements to replace  DATEADD 1 with +1 and DATEADD 2 with +2 etc then I think that would work. 

      • 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%.