Table with most recent update of quantity per depo

[adelmonte]

Hello Everyone,

I have a table with Orders, Products, Delivery Date, Email update date, Email update time, Depo1, Depo2
I receive up to 4 updates of initial order per day for one OrderID, and Delivery Date where quantity can change.
I would need a columns with the latest updated quantity, one column with old quantity per Depo1 and Depo2 the same.

Thanks in advance for your help!

RAW Table

Desired outcome

Google Spreadsheet:
https://bit.ly/3m9PdVa

[v-yiruan-msft · Accepted solution]

Hi @adelmonte ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Add a custom column for email time with date and time in Power Query Editor

2. Select all Depo_xx columns and unpivot all of these selected columns

= Table.Unpivot(#"Changed Type1", {"Depo_01", "Depo_02", "Depo_03", "Depo_04", "Depo_05", "Depo_06", "Depo_07", "Depo_08", "Depo_09", "Depo_10"}, "Depos", "Value")

3. Create the measures as below to get the value of Depo and UPD_Depo

> Get the value of Depo

Temp_Depo = 
VAR _selordid =
    SELECTEDVALUE ( 'Table'[Order ID] )
VAR _seldepo =
    SELECTEDVALUE ( 'Table'[Depos] )
VAR _maxemailtime =
    CALCULATE (
        MAX ( 'Table'[Email Datetime] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Order ID] = _selordid )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Order ID] = _selordid
                && 'Table'[Depos] = _seldepo
                && 'Table'[Email Datetime] = _maxemailtime
        )
    )

nDepo = SUMX(GROUPBY('Table','Table'[Order ID],'Table'[Depos]),[Temp_Depo])

>Get the value of UPD_Depo

Temp_UPD_Depo = 
VAR _selordid =
    SELECTEDVALUE ( 'Table'[Order ID] )
VAR _seldepo =
    SELECTEDVALUE ( 'Table'[Depos] )
VAR _minemailtime =
    CALCULATE (
        MIN ( 'Table'[Email Datetime] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Order ID] = _selordid )
    )
VAR _mindepo =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Order ID] = _selordid
                && 'Table'[Depos] = _seldepo
                && 'Table'[Email Datetime] = _minemailtime
        )
    )
RETURN
    IF ( ISBLANK ( [Temp_Depo] ), BLANK (), [Temp_Depo] - _mindepo )

UPD_Depo = SUMX(GROUPBY('Table','Table'[Order ID],'Table'[Depos]),[Temp_UPD_Depo])

But the UPD_Depo value I get is not the same as yours... Is UPD_Depo the value of the latest mail date - the value of the oldest mail date? For example, if the order id is ID210081409, the latest value of Depo_1 is 672, and the value of the oldest mail date and time is 560, the final result should be 672-560=112, but why your value is 0? If my understanding is wrong, please help me to correct and provide more explanation on the logic of UPD_Depo calculation. Thank you.

Your desired output

My gotten value

Best Regards

[adelmonte]

This is great!
Many many thanks for your help.
It works perfectly well the way you did it with the difference from last and first email.
I just wanted to know if I can repeat all item labels on Row Headers in Matrix Visual


Regards,
Alex

[amitchandak]

@adelmonte , Create a new column as flag and use that

Create a new email date time first

email date time = [email date] + [email time]

Flag =

if( [email date time] = maxx(filter(Table, [Order Id] = earlier([Order Id]))  [email date time] ),1,0)

filter for 1 in visual or measure