cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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] ) )
)
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

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] ) )
)
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

paris Regular Visitor
Regular Visitor

Re: DAX : Select latest data available

Thank you so much. It was trying to do!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 292 members 3,223 guests
Please welcome our newest community members: