topic Re: Need help to get the latest value based on latest update date. in DAX Commands and Tips

Need help to get the latest value based on latest update date.

jasonyy — Wed, 13 Mar 2024 05:34:41 GMT

Hi, I need help / advice on how to get the latest standard cost value based on last update date for different country.

I have try Calculate method, but not able to get the desired outcome on standard cost. Please help to advise what can i do.

Below are the sample table data.

This are the desired preferred outcome in power bi desktop, but i'm not able to get the latest value for standard cost.

Thanks

Re: Need help to get the latest value based on latest update date.

Dangar332 — Wed, 13 Mar 2024 05:50:45 GMT

hi, jasonyy

not sure but try below code for measure

STD_COST= var a = maxx( filter( all(table), table[item_number]=max(table[item_number])&& table[organization_code]=max(table[organization_code])&& table[currency]=max(currency) ), table[last_update_date] ) return sumx( filter( table, table[last_update_date]=a ), table[item_cost] )

Re: Need help to get the latest value based on latest update date.

jasonyy — Wed, 13 Mar 2024 06:52:19 GMT

Hi Dangar,

Thanks for your suggestion. I have tried it out, but not able to get the desired outcome.

Using the solution provided, it seem that it aggregate the value up and display accordingly as per below print screen.

Thanks

Re: Need help to get the latest value based on latest update date.

Anonymous — Thu, 14 Mar 2024 10:02:17 GMT

Hi jasonyy ,

Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create a new measure to filter.

STD_COST = VAR latest_d = CALCULATE(MAX([Lat_update_date]), ALLEXCEPT('Table', 'Table'[Organization code])) RETURN CALCULATE( SUM('Table'[ITEM cost]), FILTER('Table', 'Table'[Lat_update_date] = latest_d) )

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need help to get the latest value based on latest update date.

Dangar332 — Thu, 14 Mar 2024 10:07:52 GMT

hi, jasonyy

try updated code it might work

STD_COST= var a = maxx( filter( all(table), table[item_number]=max(table[item_number])&& table[organization_code]=max(table[organization_code])&& table[currency]=max(currency) ), table[last_update_date] ) return sumx( filter( all(table[last_update_date], table[item_cost]), table[last_update_date]=a ), table[item_cost] )