cancel
Showing results for
Did you mean:
Regular Visitor

## DAX : Select latest data available

Hi All,

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: DAX : Select latest data available

As a calculated table you can use

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

## Re: DAX : Select latest data available

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 4
Super User

## Re: DAX : Select latest data available

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

Super User

## Re: DAX : Select latest data available

As a calculated table you can use

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

## Re: DAX : Select latest data available

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

Thanks
Raj

Highlighted
Regular Visitor

## Re: DAX : Select latest data available

Thank you so much. It was trying to do!