cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver III
Resolver III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

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

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Resolver III
Resolver III

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

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. 

Highlighted
Super User I
Super User I

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

Corey,

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors