https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618070#M139873 <P>Hi,&nbsp;</P><P>I am trying to create a measure "Latest Cost" that takes the most recent cost for a SKU based on the Invoice Date.&nbsp;&nbsp;</P><P>&nbsp;</P><P>I am trying to find these values for the sample data below:</P><P>ABC123 =&nbsp;<FONT color="#FF0000">$30.20</FONT></P><P>XYZ123 =&nbsp;<FONT color="#FF0000">$50.20</FONT></P><P>BBB123 =&nbsp;<FONT color="#FF0000">$30.90</FONT></P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Supplier SKU</TD><TD>Invoice Date</TD><TD>Purchase Unit Price</TD></TR><TR><TD>ABC123</TD><TD>12/22/2022</TD><TD>$37.95</TD></TR><TR><TD>ABC123</TD><TD>1/16/2023</TD><TD>$34.95</TD></TR><TR><TD>ABC123</TD><TD><FONT color="#FF0000">11/9/2023</FONT></TD><TD><FONT color="#FF0000">$30.20</FONT></TD></TR><TR><TD>XYZ123</TD><TD><FONT color="#FF0000">10/16/2023</FONT></TD><TD><FONT color="#FF0000">$50.20</FONT></TD></TR><TR><TD>XYZ123</TD><TD>9/29/2023</TD><TD>$45.20</TD></TR><TR><TD>XYZ123</TD><TD>8/31/2023</TD><TD>$65.20</TD></TR><TR><TD>BBB123</TD><TD><FONT color="#FF0000">8/25/2023</FONT></TD><TD><FONT color="#FF0000">$30.90</FONT></TD></TR><TR><TD>BBB123</TD><TD>8/11/2023</TD><TD>$31.00</TD></TR></TBODY></TABLE> Fri, 05 Jan 2024 15:06:46 GMT Tmk123 2024-01-05T15:06:46Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618070#M139873 <P>Hi,&nbsp;</P><P>I am trying to create a measure "Latest Cost" that takes the most recent cost for a SKU based on the Invoice Date.&nbsp;&nbsp;</P><P>&nbsp;</P><P>I am trying to find these values for the sample data below:</P><P>ABC123 =&nbsp;<FONT color="#FF0000">$30.20</FONT></P><P>XYZ123 =&nbsp;<FONT color="#FF0000">$50.20</FONT></P><P>BBB123 =&nbsp;<FONT color="#FF0000">$30.90</FONT></P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Supplier SKU</TD><TD>Invoice Date</TD><TD>Purchase Unit Price</TD></TR><TR><TD>ABC123</TD><TD>12/22/2022</TD><TD>$37.95</TD></TR><TR><TD>ABC123</TD><TD>1/16/2023</TD><TD>$34.95</TD></TR><TR><TD>ABC123</TD><TD><FONT color="#FF0000">11/9/2023</FONT></TD><TD><FONT color="#FF0000">$30.20</FONT></TD></TR><TR><TD>XYZ123</TD><TD><FONT color="#FF0000">10/16/2023</FONT></TD><TD><FONT color="#FF0000">$50.20</FONT></TD></TR><TR><TD>XYZ123</TD><TD>9/29/2023</TD><TD>$45.20</TD></TR><TR><TD>XYZ123</TD><TD>8/31/2023</TD><TD>$65.20</TD></TR><TR><TD>BBB123</TD><TD><FONT color="#FF0000">8/25/2023</FONT></TD><TD><FONT color="#FF0000">$30.90</FONT></TD></TR><TR><TD>BBB123</TD><TD>8/11/2023</TD><TD>$31.00</TD></TR></TBODY></TABLE> Fri, 05 Jan 2024 15:06:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618070#M139873 Tmk123 2024-01-05T15:06:46Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618093#M139875 <P>Adjust the table name in the following DAX Measure:</P><P>&nbsp;</P><DIV><DIV><SPAN>Latest Unit Price =</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>MaxInvoiceDate</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'SKU'</SPAN><SPAN>[Invoice Date]</SPAN><SPAN>), </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'SKU'</SPAN><SPAN>, </SPAN><SPAN>'SKU'</SPAN><SPAN>[Supplier SKU]</SPAN><SPAN>))</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'SKU'</SPAN><SPAN>[Purchase Unit Price]</SPAN><SPAN>), </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'SKU'</SPAN><SPAN>, </SPAN><SPAN>'SKU'</SPAN><SPAN>[Invoice Date]</SPAN><SPAN> = </SPAN><SPAN>MaxInvoiceDate</SPAN><SPAN>))</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>If I answered your question, please mark this thread as accepted.<BR />Follow me on LinkedIn:<BR /><A href="https://www.linkedin.com/in/mustafa-ali-70133451/" target="_blank">https://www.linkedin.com/in/mustafa-ali-70133451/</A></SPAN></DIV></DIV> Fri, 05 Jan 2024 15:28:43 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618093#M139875 amustafa 2024-01-05T15:28:43Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618099#M139876 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="583197" data-lia-user-login="Tmk123" class="lia-mention lia-mention-user">Tmk123</a>&nbsp;<BR />Please try</P> <LI-CODE lang="javascript">Latest Cost = SUMX ( VALUES ( 'Table'[Supplier SKU] ), MAXX ( TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[Invoice Date] ), 'Table'[Purchase Unit Price] ) )</LI-CODE> Fri, 05 Jan 2024 15:31:04 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618099#M139876 tamerj1 2024-01-05T15:31:04Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618622#M139898 <P><img /></P> Fri, 05 Jan 2024 21:50:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3618622#M139898 ThxAlot 2024-01-05T21:50:36Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3623244#M140093 <P>Thanks so much!</P> Mon, 08 Jan 2024 14:40:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3623244#M140093 Tmk123 2024-01-08T14:40:01Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3623245#M140094 <P>Thank you!!</P> Mon, 08 Jan 2024 14:41:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-formula-to-return-most-recent-cost-for-SKU/m-p/3623245#M140094 Tmk123 2024-01-08T14:41:15Z