Using DAX to selectively pick 1 Day value from a list of possible matching days.

[rainynights]

Let's say I'm a manager at a shipping company, and each time we get a category of an item, we need to know in a table when the next shipping date is. 

The table below illustrates an example of shipping days:

Things like 'Furniture' are very easy to know the next shipment day for, because we know that since it's only going to ship on the next Wednesday. however, things like 'F&B' and 'Apparel' ship on multiple days, and that makes this tricky. 

The table I'm working with is the following:

The number in both tables represents the number associated with the day (Mon to Sun are associated with 1-7, respectively). 

The 2nd table just above shows the date we received a category of item. The 1st table gives us the info that F&B ships on both Monday and Friday, which means the F&B item we received on Tuesday will be shipping on Friday, since that is the nearer day, and the F&B item we receive on Saturday will ship on Monday, since that's the nearer day. 

Same concept applies to the Apparel category. 

Essentially, I'm trying to create a 4th column in the 2nd table above that shows when the next shipment day is, but I'm not sure how to go about doing this!

Here's a sample PBIX file (that the above 2 screenshots came from):

https://1drv.ms/u/s!AqOYqK3B8ETjcCg8SBDLJCJYKrc

[v-yuta-msft · Accepted solution]

@rainynights ,

Create a calculate column using dax below:

Nearest Day = 
VAR Product_Type = 'Product Received Day'[Product Type]
VAR Received_Day_Number = 'Product Received Day'[Received Day number]
VAR Nearest_Day_Number_Later =
    CALCULATE (
        MIN ( 'Shipment Days'[Shipment Day Number] ),
        FILTER (
            'Shipment Days',
            'Shipment Days'[Shipment Day Number] >= Received_Day_Number
                && 'Shipment Days'[Product Category] = Product_Type
        )
    )
VAR Nearest_Day_Number =
    IF (
        Nearest_Day_Number_Later <> BLANK (),
        Nearest_Day_Number_Later,
        CALCULATE (
            MIN ( 'Shipment Days'[Shipment Day Number] ),
            FILTER ( 'Shipment Days', 'Shipment Days'[Product Category] = Product_Type )
        )
    )
RETURN
    SWITCH (
        Nearest_Day_Number,
        1, "Monday",
        2, "Tuesday",
        3, "Wednesday",
        4, "Thursday",
        5, "Friday",
        6, "Saturday",
        7, "Sunday"
    )

 

You can also refer to the pbix file attached.

Community Support Team _ Jimmy Tao

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

[Greg_Deckler]

See if this does the trick:

Next Shipment = 
    VAR __Next = MAXX(FILTER('Product Received Day','Product Received Day'[Product Type] = 'Shipment Days'[Product Category] && 'Product Received Day'[Received Day number] > 'Shipment Days'[Shipment Day Number]),'Product Received Day'[Received Day number])
RETURN
    IF(
        ISBLANK(__Next),
        MINX(FILTER('Product Received Day','Product Received Day'[Product Type] = 'Shipment Days'[Product Category] && 'Product Received Day'[Received Day number] < 'Shipment Days'[Shipment Day Number]),'Product Received Day'[Received Day number]),
        __Next
    )

Your updated PBIX is attached.