Check if the newly received delivery date is better or worse than the previously received date

[GeorgeFP]

Hello, thanks for reading.

After many hours of Google, trial and error, I have to admit that I am not able to solve this and hence why I am here 🙂

We receive an Excel file from the supplier every day with updated estimated delivery dates for our orders (POs), which is appended to the "larger" list. The 1st column is updated with today's date to keep track of when we received the information. Some days, the new estimated delivery day improved from the previous day and sometimes worse (delayed).

Refer to the image of an example layout and the last two columns on the right that must be calculated.

How would I achieve this? And thank you in advance.

Regards - George

[v-chenwuz-msft · Accepted solution]

Hi @GeorgeFP ,

Have you solved your problem? If solved, please share your solution and tag it so more people can find it quickly, if not, you can refer to the solution below.

Compare to the 1st =
VAR _Filter =
    TOPN (
        1,
        FILTER ( 'POTable', [PO number] = EARLIER ( POTable[PO number] ) ),
        [Info Received from Supplier], ASC
    )
VAR _1st =
    MAXX ( _Filter, [Estimated Delivery Date] )
VAR _1stInfo =
    MAXX ( _Filter, [Info Received from Supplier] )
RETURN
    IF (
        [Info Received from Supplier] = _1stInfo,
        "-",
        SWITCH (
            TRUE (),
            [Estimated Delivery Date] > _1st, "Worse",
            [Estimated Delivery Date] = _1st, "Same",
            "Better"
        )
    )


Compare to the last =
VAR _Filter =
    TOPN (
        1,
        FILTER ( 'POTable', [PO number] = EARLIER ( POTable[PO number] ) ),
        [Info Received from Supplier], DESC
    )
VAR _last =
    MAXX ( _Filter, [Estimated Delivery Date] )
RETURN
    IF (
        [Compare to the 1st] = "-",
        "-",
        SWITCH (
            TRUE (),
            [Estimated Delivery Date] > _last, "Worse",
            [Estimated Delivery Date] = _last, "Same",
            "Better"
        )
    )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[PiEye]

Hi George

I was able to get the result by using an expression to first get the previous day's value, and then modify it to compare with the current date to produce the result:

I've used an ID field instead of info recieved from the supplier, and modified the dax pattern here to get the previous date: https://forum.enterprisedna.co/t/previous-row-value/9789

Previous date: 

PreviousDate = 
// DAX PATTERN NAME: Previous Row Value - Method 2 - Measure V2
// NOTES: Use variables to get the previous fact table date, then filter for that date
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentID = 
    SELECTEDVALUE( Dates[ID])              -- replace Sales[Order Date] with the column containing the value date
VAR _PreviousID = 
    CALCULATE( MAX( Dates[ID] ),            -- replace Sales[Order Date] with the column containing the value date
        FILTER( 
            ALLEXCEPT ( Dates,Dates[Ref] ),                   -- replace [Sales] with the table containing your values
            Dates[ID] < _CurrentID ) )    -- replace Sales[Order Date] with the column containing the value date
VAR _Result = 
    CALCULATE( max(Dates[Date]),
        FILTER(
            ALLEXCEPT ( Dates,Dates[Ref] ),                   -- replace [Sales] with the table containing your values
             Dates[ID] = _PreviousID       -- replace Sales[Order Date] with the column containing the value date
        )
)
RETURN _Result

Comparison Field:

This is the same as the Previous Date field, but there is an if statement in the return which performs the comparison and returns the text value needed:

{Same as before.....}

RETURN If(ISBLANK(_Result),"NA", iF(_Result<MAX(Dates[Date]),"Later",If(_Result>MAX(Dates[Date]),"Earlier","Same")))

Let me know if you have any questions!

Pi

[amitchandak]

@GeorgeFP , A new column like

New column =
var _max = maxx(filter(Table, [PO number] = earlier([PO Number]) && [info received from Supplier] < earlier([info received from Supplier] )), [info received from Supplier] )
var _maxDel =var _max = maxx(filter(Table, [PO number] = earlier([PO Number]) && [info received from Supplier] =_max), [Estimated Delivery Date] )
return
Switch( True() ,
_maxDel = [Estimated Delivery Date] , "Same",
_maxDel > [Estimated Delivery Date] , "Worse",
"Better"
)