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

Hi Amy,

 

I use a Date Table in my data model as I use PBI's time intelligence functions for YOY analysis. However, the date relationship is between [DATE - ORD CLOSE] as I display aggregates by month based off an order's close date. 

 

Attached is a screenshot of my Date Table. Please note the column Working Day Index. This column is referenced by another calculated column in my BANDB table, called ShipBy Date. The purpose of the ShipBy Date is to determine the deadline for this order to be closed. Essentially, if the order type is BTB, it's 3 working days from Order RCV Date, where as if the order type is DTC, it's 1 working day. Please view the screenshot of the calculated column ShipBy Date. 

 

Is there a way I can edit this for the purposes of my Order RCV Date column? So that it basically says, "if [Past Cutoff] = 1 , return Next Working Day?"

 

 Please let me know your thoughts.

Thanks!

 

P.S. I don't know why this all of the sudden would not work, as I've used this exact same data model for multiple reports. I just change the business unit in each *.pbix specific to the client I'm building the report for. ( So I duplicate the *.pbix and edit the queries for the new BU )