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] ) )
)
Super User
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
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
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] ) )
)
Super User
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
paris Regular Visitor
Regular Visitor

Re: DAX : Select latest data available

Thank you so much. It was trying to do!