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

     

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

Hi CoreyP ,

 

The DATEADD() function require contiguous date selection.

 

You can create calendar table first of all, create relationship between the 'BANDB' table and calendar table on date field, then you can use the Calendar[Date] instead of 'BANDB'[DATE - ORD RCV] in DATEADD function.

 

Calendar = CALENDAR(MIN(data[Date]),MAX(data[Date]))  
 
Or
 
Calendar =CALENDARAUTO()

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Well, I played around with the formula for my ShipBy Date column and it's returning correct values! So, good news, I figured out a solution, however, I really would like to know why the original method just stopped working when it has been successful in multiple reports until now.

 

Here is how I edited the ShipBy: