Forum Discussion

Grevatious's avatar
Grevatious
Frequent Visitor
6 years ago
Solved

Get the last delivery date

Hello everyone,

 

I need some help! I have two tables. The first one has all of the order description and client information and the other shows all the information regarding the delivery of each order. It is possible that each order has 2 or more Tracking Numbers with different delivery dates.

 

What I'm trying to do is to get the last delivery date from table 2 for each order of the table 1.

 

I have tried many times to no avail, so right I'm using the "Firstnoblank" formula, but this only returns the first value it finds instead of the last one by date.

 

Table 2:

Delivery Information
OrderTracking NumberDelivery Date
123100011/1/2020
124100022/1/2020
125100033/1/2020
126100044/1/2020
125100055/1/2020
123100066/1/2020

 

Table 1:

Orders
OrderLast Delivery Date
1236/1/2020
1242/1/2020
1255/1/2020
1264/1/2020

 

Thank you all!

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Grevatious ,

     

    Would you please refer to the following measure:

    last delivery date =

CALCULATE (

    MAX ( 'Delivery Information'[Delivery Date] ),

    ALL ( 'Delivery Information' ),

    'Delivery Information'[Order] IN DISTINCT ( Orders[Order] )

)

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

     

    Best Regards,

    Dedmon Dai

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Probably in Table1,

     

    Last delivery date = MAXX(FILTER(RELATEDTABLE('Table2'),'Table2'[Order]=[Order]),[Delivery date])

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Grevatious ,

    Use this measure in you table to display the date

     

     

    Measure =
    VAR __id = MAX( 'Table'[Orders] )
    VAR __date = CALCULATE ( MAX( 'Table'[Delivery date] ), ALLSELECTED ( 'Table' ), 'Table'[Orders] = __id )
    RETURN CALCULATE ( MAx ( 'Table'[Delivery date] ), VALUES ( 'Table'[Orders] ), 'Table'[Orders] = __id, 'Table'[Delivery date] = __date )

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Grevatious ,

     

    Would you please refer to the following measure:

    last delivery date =

CALCULATE (

    MAX ( 'Delivery Information'[Delivery Date] ),

    ALL ( 'Delivery Information' ),

    'Delivery Information'[Order] IN DISTINCT ( Orders[Order] )

)

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

     

    Best Regards,

    Dedmon Dai

  • PowerBIIE's avatar
    PowerBIIE
    Frequent Visitor
    4 years ago

    Hey - I want to use something like this for a calculated column. If I use this measure, it does not work correctly, how would I work around it?