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 Contributor

## 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 Contributor

## 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 Contributor

## 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

Regular Visitor

## Re: DAX : Select latest data available

Thank you so much. It was trying to do!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 56 members 1,274 guests
Recent signins: