Weird behavior with DATEADD! Please HELP!

[CoreyP]

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 giving me pains now as I can't figure out what is wrong!

Here is what's happening. I am pulling shipping data from a warehouse management system, then modeling for my purposes. I took the Order RCV TStamp column ( from WMS ), duplicated, and split so that I have two columns, DATE - ORD RCV, and TIME - ORD RCV. Then I'm creating a calculated column to change the order receive date depending on when the order was received. So, the cutoff time is 11:00AM. If an order is received after 11am, it is considered past cutoff, and therefore the date should be moved to the next work day. As you can see, my column adds either 1 day, 3 days, or 2 days, depending on what day of the week it falls on. For example, if it's a tuesday, and the order was received past cutoff, just add one business day to make it wednesday. However, if the order is received past cutoff on a friday, add 3 days to make the order receive date Monday. You can see in my calculated column, Order RCV Date, that the calculation is returning a bunch of blank values ( first time this has happened ). To test, I created another column called Order RCV TEST, which is a simpler DATEADD function without any logic, simply adding 1 Day to the DATE - ORD RCV. As you can see in this test column, it is also returning a ton of blank values.

I can't wrap my head around what is happening here. Can someone please shed some light on this? Let me know if you need any other data or screenshots. I cannot share my *.pbix as we're dealing with propietary customer information. Really appreciate you looking into this for me!

[v-xicai]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

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:

[CoreyP]

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 )