topic Re: Calculate Compensation End Dates of an employee with multiple Compensation Start Dates in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2067088#M46721 <P>You're very welcome!</P><P>&nbsp;</P><P>Cheers</P> Thu, 09 Sep 2021 15:09:05 GMT Jos_Woolley 2021-09-09T15:09:05Z Calculate Compensation End Dates of an employee with multiple Compensation Start Dates https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060014#M46467 <P>Hi all,</P><P>&nbsp;</P><P>I need your help creating a correct Compensation End date for my data.&nbsp;</P><TABLE><TBODY><TR><TD>Employee Id</TD><TD>Employee Name</TD><TD>Compensation Start Date</TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>01/01/2020</TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>12/14/2020</TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>02/01/2021</TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>07/01/2021</TD></TR><TR><TD>100290</TD><TD>Jessi Park</TD><TD>06/08/2020</TD></TR><TR><TD>100290</TD><TD>Jessi Park</TD><TD>05/01/2021</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I created a calculated column:</P><DIV><DIV class="lia-indent-padding-left-60px"><DIV><DIV><SPAN>Compensation End Date = </SPAN></DIV><DIV><SPAN>VAR maxdate =</SPAN></DIV><DIV><SPAN>CALCULATE (</SPAN></DIV><DIV><SPAN>MAX (Compensation[Compensation_Start_Date__c] ),</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Compensation,</SPAN></DIV><DIV><SPAN>Compensation[Employee__r.Id] = EARLIER(Compensation[Employee__r.Id])</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>IF ( Compensation[Compensation_Start_Date__c] = maxdate, BLANK(), maxdate - 1 )</SPAN></DIV></DIV></DIV><DIV>&nbsp;</DIV><DIV>Result:&nbsp;</DIV><DIV><TABLE><TBODY><TR><TD>Employee Id</TD><TD>Employee Name</TD><TD>Compensation Start Date</TD><TD><EM>Compensation End Date</EM></TD><TD><STRONG>Expected Compensation End Date</STRONG></TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>01/01/2020</TD><TD><EM>06/302021</EM></TD><TD><STRONG>12/13/2020</STRONG></TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>12/14/2020</TD><TD><EM>06/302021</EM></TD><TD><STRONG>01/31/2020</STRONG></TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>02/01/2021</TD><TD><EM>06/30/2021</EM></TD><TD><STRONG>06/30/2021</STRONG></TD></TR><TR><TD>100010</TD><TD>David Mayer</TD><TD>07/01/2021</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>100290</TD><TD>Jessi Park</TD><TD>06/08/2020</TD><TD><EM>04/30/2021</EM></TD><TD><STRONG>04/30/2021</STRONG></TD></TR><TR><TD>100290</TD><TD>Jessi Park</TD><TD>05/01/2021</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE></DIV><DIV>&nbsp;</DIV><DIV><SPAN>The formula is doing fine for records with only 2 rows, however, it is not giving me the desired result for employees with &gt;2 records. How can I tweak my formula to return the desired result?&nbsp; Thank you in advance for your help.<BR /><BR /></SPAN></DIV><DIV><SPAN>Regards,</SPAN></DIV><DIV><SPAN>Kat</SPAN></DIV></DIV> Mon, 06 Sep 2021 14:37:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060014#M46467 Anonymous 2021-09-06T14:37:38Z Re: Calculate Compensation End Dates of an employee with multiple Compensation Start Dates https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060192#M46472 <P>Hi,</P><P>Perhaps this:</P><LI-CODE lang="markup">Compensation End Date = VAR NextDate = CALCULATE ( MIN ( Compensation[Compensation Start Date] ), FILTER ( Compensation, Compensation[Employee Id] = EARLIER ( Compensation[Employee Id] ) &amp;&amp; Compensation[Compensation Start Date] &gt; EARLIER ( Compensation[Compensation Start Date] ) ) ) RETURN IF ( NextDate = 0, BLANK (), NextDate - 1 )</LI-CODE><P>Regards</P> Mon, 06 Sep 2021 16:03:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060192#M46472 Jos_Woolley 2021-09-06T16:03:23Z Re: Calculate Compensation End Dates of an employee with multiple Compensation Start Dates https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060264#M46478 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317022" data-lia-user-login="Jos_Woolley" class="lia-mention lia-mention-user">Jos_Woolley</a>&nbsp;It worked perfectly!! Thank you so so much for taking the time to solve my problem!&nbsp;<span class="lia-unicode-emoji" title=":smiling_face_with_smiling_eyes:">😊</span></P> Mon, 06 Sep 2021 17:14:58 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2060264#M46478 Anonymous 2021-09-06T17:14:58Z Re: Calculate Compensation End Dates of an employee with multiple Compensation Start Dates https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2067088#M46721 <P>You're very welcome!</P><P>&nbsp;</P><P>Cheers</P> Thu, 09 Sep 2021 15:09:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-Compensation-End-Dates-of-an-employee-with-multiple/m-p/2067088#M46721 Jos_Woolley 2021-09-09T15:09:05Z