Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get the latest value from a column based on another date column

Hi,
I have a table that looks similar to this.

ItemDatePrice
A1st July 2020300
A10th May 2020100
A4th Aug 2020200
B3rd Mar 2020450
B15th Sept 2020560


I want to get the latest value for 'Price' for each item.
The output should look like this

ItemLatest Price
A200
B560

 

How can this be achieved?

Need Help

7 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    you can it it like this:

     

     

    Latest Price = 
CALCULATE(
    MAX('Table'[Price]),
    FILTER(
        ALLEXCEPT('Table','Table'[Item]),
        MAX('Table'[Date]) = 'Table'[Date]
    )
)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for the answer, 
      I have a few follow-up questions,

      How can I get the other column values of the row which had the latest price value for the item?
      Also, I want to group the rows and find the latest price for the item as well as the group. 
      Similar to the example I have shown below

      ItemGrpDate TagPrice
      A110th May 2020Tag1670
      A214th June 2020Tag2100
      A21st April 2020Tag3500
      A213th August 2020Tag4400
      A15th Sept 2020Tag5590
      B14th Feb 2020Tag6110


      The output if possible should create another table, with the following output. 

      ItemGrpDateTagLatest Price
      A15th Sept 2020Tag5590
      A23rd August 2020Tag4400
      B14th Feb 2020Tag6110



      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        hi Anonymous - You can follow the below steps to achieve the required results. 

         

        1. Create a calculated column Item + Group

        Item&Grp = 'Test Table'[Item] & "-" & 'Test Table'[Grp]
         
        2. Update the Rank measure as below 
        Date Rank =
        IF (
        HASONEVALUE ( 'Test Table'[Item&Grp] ),
        RANKX ( ALLSELECTED ( 'Test Table'[Date], 'Test Table'[Tag] ), ( [Sort Date] ), ,DESC, Skip )
        )
        Filter the specific visual for records where "Date Rank" = 1 as seen below 
         Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi Anonymous - you can create a measure to rank all records by Date and Item as shown below

    1. Sort Date = MAX( 'Test Table'[Date] )

    2. Date Rank =

    IF (
    HASONEVALUE ( 'Test Table'[Item] ),
    RANKX ( ALLSELECTED ( 'Test Table'[Date] ), ( [Sort Date] ), ,DESC, Skip )
    )
     3. "Date Rank" creates a rank for each Item as seen below
    4. Add a visual level filter to show only records where "Date Rank" = 1 as seen below
     
  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

    If not, please kindly elaborate more.