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

[jasonyy]

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

[Anonymous · Accepted solution]

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.

[Dangar332]

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

[jasonyy]

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

[Dangar332]

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