Possibly, Maybe, a Lookup or something? Check out what I'm trying to do...

[CoreyP]

Hey guys!! 

You might have seen my previous post about adding working days. This is still part of the same problem, however, I've established a method that I need help writing the correct code for. 

Assuming my date table looks like this: (May 9th is a holiday for example purposes)

Date     Workday	Index
5/7/2018	1	1
5/8/2018	1	2
5/9/2018	0	
5/10/2018	1	3
5/11/2018	1	4
5/12/2018	0	
5/13/2018	0	

And my orders table looks like this:

OrderNumber  	Order Type   	Order Received Date	ShipBy Date
000000	                 Retail	           5/7/2018	
111111	                 Wholesale	   5/7/2018	
222222	                 Retail	           5/7/2018	
333333	                 Wholesale	   5/7/2018	
444444	                 Retail	           5/7/2018	
555555	                 Wholesale	   5/8/2018	
666666	                 Retail	           5/8/2018	
777777	                 Wholesale	   5/8/2018	
888888	                 Retail	           5/8/2018	
999999	                 Wholesale	   5/8/2018	

The, currently empty, ShipBy Date column, is where I'm wanting to put a DAX calc column that:

IF Order Type is Retail, return the next sequential Index number after the Order Received Date 

and

IF Order Type is Wholesale, return the 3rd sequential Index number after the Order Received Date

Furthermore... I'm wanting it to actually return the corresponding Date of the returned Index number, but I think I can manage that part on my own. Though, if you wanted to include it, awesomesauce. Now, there might be a problem here... if an Order Received Date lands on a weekend, or holiday, the Index is null. Not sure how that will affect it. Maybe the first step is to create a calculated column that says:

IF the DateTable[workday] of Order Received Date is ZERO, then return the Date of the next NONBLANK [Index] AFTER Order Received Date...

I hope this makes sense... Can anyone help me out here? MUCHO appreciado, in advance..

[OwenAuger · Accepted solution]

Hey @CoreyP

Here's my attempt. There would be multiple ways to do this but this works for me in a mocked-up model (saved here).

I created the ShipBy Date as a DAX calculated column:

ShipBy Date = 
VAR OrderReceivedDate = Orders[Order Received Date]
VAR NextIndex =
    CALCULATE ( MIN ( DateTable[Index] ), DateTable[Date] > OrderReceivedDate )
VAR SelectedIndex =
    SWITCH ( Orders[Order Type], "Retail", NextIndex, "Wholesale", NextIndex + 2 )
RETURN
    IF (
        NOT ( ISBLANK ( NextIndex ) ),
        CALCULATE (
            SELECTEDVALUE ( DateTable[Date] ),
            DateTable[Index] = SelectedIndex,
            ALL ( DateTable )
        )
    )

NextIndex finds the next Index after the Order Received Date, so it doesn't matter if Order Received Date corresponds to a blank Index.

Well, give this a go and would be interested if this works for you!

Regards,

Owen

[CoreyP]

Owen,

You sir, are not only a gentleman, but a scholar!! 

I can't thank you enough! This works flawlessly!! This is EXACTLY what I was looking for! I made one minor change, and that is adding this column:

ORDER RCV DATE = 
                    IF(
                                WEEKDAY(B3[Order Received Date], 2) = 6, B3[Order Received Date] + 2,
                    IF(    
                                WEEKDAY(B3[Order Received Date], 2) = 7, B3[Order Received Date] + 1,
                  
                                B3[Order Received Date] 
                        ))

Then I just pointed your ShipBy Date to ^^^that Order Received Date, which led to me not finding a SINGLE incorrect value during my data validation. WOOOPIE!! (I did that because, if an order comes in on the weekend, let's say a retail one, then technically, we have until the following Tuesday to get it out, not Monday. 

[OwenAuger]

Corey,

That's great news that it did the trick! You're most welcome