DAX : Select latest data available

[paris]

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 · Accepted solution]

@paris 

As a calculated table you can use

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

[Anonymous]

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 · Accepted solution]

@paris 

As a calculated table you can use

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

[paris]

Thank you so much. It was trying to do!

[Anonymous · Accepted solution]

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

Thanks
Raj