Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id"

[FrankWe]

Hello,

I would like to create a calculated column called "Latest Rate". This column should contain the latest value from column "Rate" per ID in column "_id". All rows with the same ID should all have the latest value of column "Rate" in the calculated column "Latest Rate". 

What I have so far is, that I am able to find out the last date per _id with the following function:

LastDatePerID = CALCULATE(

    MAX( [Date] ),

    ALLEXCEPT( MyDataTable, MyDataTable[_id])

    )

How can I now get the value from column "Rate" for the "LastDatePerID" and write it to all rows to column "Latest Rate" having the same _id?

I hope that the example table below will help to understand what I would like to create.

_id	Date	Rate	Latest Rate
1	01.01.2024	295,00 €	287,00 €
1	03.01.2024	290,00 €	287,00 €
1	04.01.2024	287,00 €	287,00 €
2	02.01.2024	345,00 €	365,00 €
2	03.01.2024	365,00 €	365,00 €
3	01.01.2024	145,00 €	125,00 €
3	02.01.2024	153,00 €	125,00 €
3	05.01.2024	139,00 €	125,00 €
3	07.01.2024	125,00 €	125,00 €

Many thanks in advance for your help.

Frank

[Dangar332]

hi, @FrankWe 

try below

latest rate =
var LastDatePerID = 
     CALCULATE(
    MAX( [Date] ),
    ALLEXCEPT( MyDataTable, MyDataTable[_id])
    )   -- your code for latest date per id

return
calculate(
   max(MyDataTable[Rate]),
   values(MyDataTable[_id]),
   MyDataTable[Date]=  LastDatePerID 
)

[FrankWe]

Hi,

I tried it but it is not doing what I need to have. The result now looks like this:

_id	Date	Rate	Latest Rate
1	01.01.2024	295,00 €
1	03.01.2024	290,00 €
1	04.01.2024	287,00 €	287,00 €
2	02.01.2024	345,00 €
2	03.01.2024	365,00 €	365,00 €
3	01.01.2024	145,00 €
3	02.01.2024	153,00 €
3	05.01.2024	139,00 €
3	07.01.2024	125,00 €	125,00 €

But I need to have this

_id	Date	Rate	Latest Rate
1	01.01.2024	295,00 €	287,00 €
1	03.01.2024	290,00 €	287,00 €
1	04.01.2024	287,00 €	287,00 €
2	02.01.2024	345,00 €	365,00 €
2	03.01.2024	365,00 €	365,00 €
3	01.01.2024	145,00 €	125,00 €
3	02.01.2024	153,00 €	125,00 €
3	05.01.2024	139,00 €	125,00 €
3	07.01.2024	125,00 €	125,00 €

With your solution a value is only written in column "Latest Rate" when the row contains the latest date in column "Date".  But I need to have the value written to all rows having the same id in column "_id"

[Dangar332 · Accepted solution]

hi, @FrankWe 

try below

Column = 
var a = MAXX( FILTER('Table (2)', [id]=EARLIER([id])),[date])
var b = MAXX( FILTER('Table (2)',[id]= EARLIER([id]) && [date]=a),[rate])
return
b

[FrankWe]

Hi Dangar332,

I tried your solution and it is working 😀 

Many thanks for your help. 

Frank