https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-to-Retrieve-previous-date-where-value-was/m-p/4128283#M163953 <P>Hi all,<BR /><BR />I am attempting to find&nbsp;previous last_run date where the position value was different in a records table based on the ID and product being the same. So following the table below, when the position value column was last different and have that shown in a table as a calculated column.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>id</TD><TD>position</TD><TD>product</TD><TD>last_run</TD><TD>Desired Column - Previous Position</TD><TD>Desired Column - Previous date</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Bottom</TD><TD>Product A</TD><TD>01-Sep-24</TD><TD>Middle</TD><TD>01-Jun-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>09-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-May-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-Apr-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-Mar-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Feb-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Jan-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Dec-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Nov-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Bottom</TD><TD>Product B</TD><TD>01-Sep-24</TD><TD>Middle</TD><TD>01-Jun-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>09-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-May-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-Apr-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-Mar-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Feb-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Jan-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Dec-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Nov-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR></TBODY></TABLE><P><BR /><BR />I have attempted using MAX function in a calculated column but I dont think I am getting the variables right.&nbsp;</P><P>Would be great to get your assistance <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>Thank you</P> Sun, 01 Sep 2024 12:34:33 GMT CRanjan12 2024-09-01T12:34:33Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-to-Retrieve-previous-date-where-value-was/m-p/4128283#M163953 <P>Hi all,<BR /><BR />I am attempting to find&nbsp;previous last_run date where the position value was different in a records table based on the ID and product being the same. So following the table below, when the position value column was last different and have that shown in a table as a calculated column.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>id</TD><TD>position</TD><TD>product</TD><TD>last_run</TD><TD>Desired Column - Previous Position</TD><TD>Desired Column - Previous date</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Bottom</TD><TD>Product A</TD><TD>01-Sep-24</TD><TD>Middle</TD><TD>01-Jun-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>09-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>15-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-May-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-Apr-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product A</TD><TD>01-Mar-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Feb-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Jan-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Dec-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product A</TD><TD>01-Nov-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Bottom</TD><TD>Product B</TD><TD>01-Sep-24</TD><TD>Middle</TD><TD>01-Jun-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Aug-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>09-Jul-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>15-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Jun-24</TD><TD>Top</TD><TD>01-May-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-May-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-Apr-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Top</TD><TD>Product B</TD><TD>01-Mar-24</TD><TD>Middle</TD><TD>01-Feb-24</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Feb-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Jan-24</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Dec-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR><TR><TD>0012000000bkPfwAAE</TD><TD>Middle</TD><TD>Product B</TD><TD>01-Nov-23</TD><TD>Middle</TD><TD>01-Nov-23</TD></TR></TBODY></TABLE><P><BR /><BR />I have attempted using MAX function in a calculated column but I dont think I am getting the variables right.&nbsp;</P><P>Would be great to get your assistance <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>Thank you</P> Sun, 01 Sep 2024 12:34:33 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-to-Retrieve-previous-date-where-value-was/m-p/4128283#M163953 CRanjan12 2024-09-01T12:34:33Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-to-Retrieve-previous-date-where-value-was/m-p/4128325#M163954 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="800011" data-lia-user-login="CRanjan12" class="lia-mention lia-mention-user">CRanjan12</a>&nbsp;See my article on Mean Time Between Failure (MTBF) which uses EARLIER. In this case, you would just need to stop at PreviousDate variable (see below). And of course include your ID and Product name in your filter.</P> <P>&nbsp;</P> <P><A href="http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586" target="_blank" rel="noopener">http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586</A>.<BR />The basic pattern is:<BR />Column =&nbsp;<BR />&nbsp; VAR __Current = [Value]<BR />&nbsp; VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] &lt; EARLIER('Table'[Date])),[Date])<BR /><BR />&nbsp; VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])<BR />RETURN<BR />&nbsp;( __Current - __Previous ) * 1.</P> Sun, 01 Sep 2024 14:27:42 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculated-Column-to-Retrieve-previous-date-where-value-was/m-p/4128325#M163954 Greg_Deckler 2024-09-01T14:27:42Z