Forum Discussion

Helper V

7 years ago

Solved

DAX : Select latest data available

Hi All,

I would like to select latest data from sales table below.

Could you please help me to write DAX for new table?

Sales Table

Date City Value

1 Jan 2018 London 100

1 Feb 2018 London 80

1 Mar 2018 London 20

1 Jan 2018 Paris 50

1 Feb 2018 Paris 30

1 Jan 2018 Tokyo 10

1 Feb 2018 Tokyo 20

1 Mar 2018 Tokyo 40

1 Apr 2018 Tokyo 100

Result I want to show

1 Mar 2018 London 80

1 Feb 2018 Paris 30

1 Apr 2018 Tokyo 100

Thank you very much for your help!

All the best,
Paris

Zubair_Muhammad

As a calculated table you can use

Table =
FILTER (
    Table1,
    Table1[Date]
        = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[City] ) )
)

Anonymous
7 years ago
Solution provided by Zubair_Muhammad is right, my formula should be wrapped by FILTER to get the right numbers for "Values" column.

Thanks
Raj

4 Replies

Anonymous
Not applicable
7 years ago
Hi

Create the below measure :

latest date = CALCULATE(MAX('Sales Table'[Date]),ALLEXCEPT('Sales Table','Sales Table'[City]))

and then pull City, value & latest Date in your visual.

Thanks

Raj
- Zubair_Muhammad
  Community Champion
  7 years ago
  paris
  
  As a calculated table you can use
  
  Table = FILTER ( Table1, Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[City] ) ) )
  - Anonymous
    Not applicable
    7 years ago
    Solution provided by Zubair_Muhammad is right, my formula should be wrapped by FILTER to get the right numbers for "Values" column.
    
    Thanks
    Raj

Related content