cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paris Regular Visitor
Regular Visitor

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: DAX : Select latest data available

@paris 

 

As a calculated table you can use

 

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

View solution in original post

rajendran Super Contributor
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

View solution in original post

4 REPLIES 4
rajendran Super Contributor
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
Super User

Re: DAX : Select latest data available

@paris 

 

As a calculated table you can use

 

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

View solution in original post

rajendran Super Contributor
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

View solution in original post

paris Regular Visitor
Regular Visitor

Re: DAX : Select latest data available

Thank you so much. It was trying to do!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 56 members 1,274 guests
Please welcome our newest community members: