topic Need a Calculated Column &quot;Latest Rate&quot; containing latest value of column &quot;Rate&quot; per value in&quot;_id&quot; in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632707#M140505 <P>Hello,</P><P>&nbsp;</P><P>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".&nbsp;</P><P>&nbsp;</P><P>What I have so far is, that I am able to find out the last date per _id with the following function:</P><P>&nbsp;</P><DIV><DIV><SPAN>LastDatePerID</SPAN><SPAN>&nbsp;= </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>MAX</SPAN><SPAN>(&nbsp;</SPAN><SPAN>[Date]&nbsp;</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>( My</SPAN><SPAN>DataTable</SPAN><SPAN>, </SPAN><SPAN>MyDataTable</SPAN><SPAN>[_id]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>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?</SPAN></DIV></DIV><P>&nbsp;</P><P>I hope that the example table below will help to understand what I would like to create.</P><P>&nbsp;</P><DIV><DIV><DIV><DIV><TABLE border="1" cellspacing="0" cellpadding="0"><TBODY><TR><TD><P>_id</P></TD><TD><P>Date</P></TD><TD><P>Rate</P></TD><TD><P>Latest Rate</P></TD></TR><TR><TD><P>1</P></TD><TD><P>01.01.2024</P></TD><TD><P>295,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>03.01.2024</P></TD><TD><P>290,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>04.01.2024</P></TD><TD><P>287,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>02.01.2024</P></TD><TD><P>345,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>03.01.2024</P></TD><TD><P>365,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>01.01.2024</P></TD><TD><P>145,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>02.01.2024</P></TD><TD><P>153,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>05.01.2024</P></TD><TD><P>139,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>07.01.2024</P></TD><TD><P>125,00 €</P></TD><TD><P>125,00 €</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Many thanks in advance for your help.</P><P>&nbsp;</P><P>Frank</P></DIV></DIV></DIV></DIV> Thu, 11 Jan 2024 07:59:46 GMT FrankWe 2024-01-11T07:59:46Z Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id" https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632707#M140505 <P>Hello,</P><P>&nbsp;</P><P>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".&nbsp;</P><P>&nbsp;</P><P>What I have so far is, that I am able to find out the last date per _id with the following function:</P><P>&nbsp;</P><DIV><DIV><SPAN>LastDatePerID</SPAN><SPAN>&nbsp;= </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>MAX</SPAN><SPAN>(&nbsp;</SPAN><SPAN>[Date]&nbsp;</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>( My</SPAN><SPAN>DataTable</SPAN><SPAN>, </SPAN><SPAN>MyDataTable</SPAN><SPAN>[_id]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>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?</SPAN></DIV></DIV><P>&nbsp;</P><P>I hope that the example table below will help to understand what I would like to create.</P><P>&nbsp;</P><DIV><DIV><DIV><DIV><TABLE border="1" cellspacing="0" cellpadding="0"><TBODY><TR><TD><P>_id</P></TD><TD><P>Date</P></TD><TD><P>Rate</P></TD><TD><P>Latest Rate</P></TD></TR><TR><TD><P>1</P></TD><TD><P>01.01.2024</P></TD><TD><P>295,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>03.01.2024</P></TD><TD><P>290,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>04.01.2024</P></TD><TD><P>287,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>02.01.2024</P></TD><TD><P>345,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>03.01.2024</P></TD><TD><P>365,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>01.01.2024</P></TD><TD><P>145,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>02.01.2024</P></TD><TD><P>153,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>05.01.2024</P></TD><TD><P>139,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>07.01.2024</P></TD><TD><P>125,00 €</P></TD><TD><P>125,00 €</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Many thanks in advance for your help.</P><P>&nbsp;</P><P>Frank</P></DIV></DIV></DIV></DIV> Thu, 11 Jan 2024 07:59:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632707#M140505 FrankWe 2024-01-11T07:59:46Z Re: Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id" https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632742#M140506 <P>hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="632063" data-lia-user-login="FrankWe" class="lia-mention lia-mention-user">FrankWe</a>&nbsp;</P><P>&nbsp;</P><P>try below</P><LI-CODE lang="markup">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 )</LI-CODE> Thu, 11 Jan 2024 08:10:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632742#M140506 Dangar332 2024-01-11T08:10:10Z Re: Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id" https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632801#M140510 <P>Hi,</P><P>I tried it but it is not doing what I need to have. The result now looks like this:</P><P>&nbsp;</P><DIV><DIV><DIV><DIV><TABLE border="1" cellspacing="0" cellpadding="0"><TBODY><TR><TD><P>_id</P></TD><TD><P>Date</P></TD><TD><P>Rate</P></TD><TD><P>Latest Rate</P></TD></TR><TR><TD><P>1</P></TD><TD><P>01.01.2024</P></TD><TD><P>295,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>1</P></TD><TD><P>03.01.2024</P></TD><TD><P>290,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>1</P></TD><TD><P>04.01.2024</P></TD><TD><P>287,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>02.01.2024</P></TD><TD><P>345,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>2</P></TD><TD><P>03.01.2024</P></TD><TD><P>365,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>01.01.2024</P></TD><TD><P>145,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>3</P></TD><TD><P>02.01.2024</P></TD><TD><P>153,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>3</P></TD><TD><P>05.01.2024</P></TD><TD><P>139,00 €</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>3</P></TD><TD><P>07.01.2024</P></TD><TD><P>125,00 €</P></TD><TD><P>125,00 €</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>But I need to have this</P><P>&nbsp;</P><DIV><DIV><DIV><DIV><TABLE border="1" cellspacing="0" cellpadding="0"><TBODY><TR><TD><P>_id</P></TD><TD><P>Date</P></TD><TD><P>Rate</P></TD><TD><P>Latest Rate</P></TD></TR><TR><TD><P>1</P></TD><TD><P>01.01.2024</P></TD><TD><P>295,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>03.01.2024</P></TD><TD><P>290,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>1</P></TD><TD><P>04.01.2024</P></TD><TD><P>287,00 €</P></TD><TD><P>287,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>02.01.2024</P></TD><TD><P>345,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>2</P></TD><TD><P>03.01.2024</P></TD><TD><P>365,00 €</P></TD><TD><P>365,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>01.01.2024</P></TD><TD><P>145,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>02.01.2024</P></TD><TD><P>153,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>05.01.2024</P></TD><TD><P>139,00 €</P></TD><TD><P>125,00 €</P></TD></TR><TR><TD><P>3</P></TD><TD><P>07.01.2024</P></TD><TD><P>125,00 €</P></TD><TD><P>125,00 €</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>With your solution a value is only written in column "Latest Rate" when the row contains the latest date in column "Date".&nbsp; But I need to have the value written to all rows having the same id in column "_id"</P></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV> Thu, 11 Jan 2024 08:27:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632801#M140510 FrankWe 2024-01-11T08:27:32Z Re: Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id" https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632883#M140514 <P>hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="632063" data-lia-user-login="FrankWe" class="lia-mention lia-mention-user">FrankWe</a>&nbsp;<BR /><BR />try below</P><P>&nbsp;</P><LI-CODE lang="markup">Column = var a = MAXX( FILTER('Table (2)', [id]=EARLIER([id])),[date]) var b = MAXX( FILTER('Table (2)',[id]= EARLIER([id]) &amp;&amp; [date]=a),[rate]) return b</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P> Thu, 11 Jan 2024 09:03:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3632883#M140514 Dangar332 2024-01-11T09:03:38Z Re: Need a Calculated Column "Latest Rate" containing latest value of column "Rate" per value in"_id" https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3633029#M140518 <P>Hi&nbsp;<SPAN>Dangar332,</SPAN></P><P>&nbsp;</P><P><SPAN>I tried your solution and it is working&nbsp;<span class="lia-unicode-emoji" title=":grinning_face:">😀</span>&nbsp;</SPAN></P><P>&nbsp;</P><P><SPAN>Many thanks for your help.&nbsp;</SPAN></P><P>&nbsp;</P><P><SPAN>Frank</SPAN></P> Thu, 11 Jan 2024 09:47:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-a-Calculated-Column-quot-Latest-Rate-quot-containing-latest/m-p/3633029#M140518 FrankWe 2024-01-11T09:47:55Z