topic Re: New Joiners Count in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979161#M154284 <P>Hi I think below will be your expected out based on data. Feb count will be 3 , Khurram, Arbaas, Waseem</P><P><img /></P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P> Fri, 07 Jun 2024 08:43:56 GMT Rupak_bi 2024-06-07T08:43:56Z New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3976789#M154189 <P>Dear Experts,<BR /><BR /></P><P>Need to calculate how many employees joined each month but the trick is in the following table example: <EM><STRONG>(Remarks column is given for help)</STRONG></EM></P><TABLE><TBODY><TR><TD>S.No</TD><TD>Payroll Month</TD><TD>Code</TD><TD>Name&nbsp;</TD><TD>CNIC&nbsp;</TD><TD>Hiring Date</TD><TD>Remarks</TD></TR><TR><TD>1</TD><TD>1-Jan-24</TD><TD>10438</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>1-Jan-24</TD><TD>Hired</TD></TR><TR><TD>2</TD><TD>1-Jan-24</TD><TD>10320</TD><TD>Safdar</TD><TD>45555887772</TD><TD>1-Feb-23</TD><TD>&nbsp;</TD></TR><TR><TD>3</TD><TD>1-Jan-24</TD><TD>10553</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>1-Oct-23</TD><TD>&nbsp;</TD></TR><TR><TD>4</TD><TD>1-Jan-24</TD><TD>10042</TD><TD>Hussain</TD><TD>45555887774</TD><TD>1-Jan-24</TD><TD>HIred</TD></TR><TR><TD>5</TD><TD>1-Feb-24</TD><TD>10438</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>1-Jan-24</TD><TD>&nbsp;</TD></TR><TR><TD>6</TD><TD>1-Feb-24</TD><TD>10320</TD><TD>Safdar</TD><TD>45555887772</TD><TD>1-Feb-23</TD><TD>&nbsp;</TD></TR><TR><TD>7</TD><TD>1-Feb-24</TD><TD>10553</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>1-Oct-23</TD><TD>&nbsp;</TD></TR><TR><TD>8</TD><TD>1-Feb-24</TD><TD>10042</TD><TD>Hussain</TD><TD>45555887774</TD><TD>1-Jan-24</TD><TD>&nbsp;</TD></TR><TR><TD>9</TD><TD>1-Feb-24</TD><TD>10953</TD><TD>Waseem</TD><TD>45555887776</TD><TD>1-Feb-24</TD><TD>Hired</TD></TR><TR><TD>10</TD><TD>1-Feb-24</TD><TD>10993</TD><TD>khurram</TD><TD>45555887777</TD><TD>1-Feb-24</TD><TD>hired</TD></TR><TR><TD>11</TD><TD>1-Mar-24</TD><TD>10555</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>1-Mar-24</TD><TD>Confirmed with new Emp ID, CNIC is same</TD></TR><TR><TD>12</TD><TD>1-Mar-24</TD><TD>10556</TD><TD>Hussain</TD><TD>45555887774</TD><TD>1-Mar-24</TD><TD>Confirmed with new Emp ID, CNIC is same</TD></TR><TR><TD>13</TD><TD>1-Mar-24</TD><TD>10399</TD><TD>Arbaas</TD><TD>45555887775</TD><TD>1-Feb-24</TD><TD>&nbsp;</TD></TR><TR><TD>14</TD><TD>1-Mar-24</TD><TD>10993</TD><TD>khurram</TD><TD>45555887777</TD><TD>1-Feb-24</TD><TD>&nbsp;</TD></TR><TR><TD>15</TD><TD>1-Mar-24</TD><TD>10959</TD><TD>maheen</TD><TD>45555887780</TD><TD>1-Mar-24</TD><TD>Hired</TD></TR><TR><TD>16</TD><TD>1-Apr-24</TD><TD>10555</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>1-Mar-24</TD><TD>&nbsp;</TD></TR><TR><TD>17</TD><TD>1-Apr-24</TD><TD>10556</TD><TD>Hussain</TD><TD>45555887774</TD><TD>1-Mar-24</TD><TD>&nbsp;</TD></TR><TR><TD>18</TD><TD>1-Apr-24</TD><TD>10959</TD><TD>maheen</TD><TD>45555887780</TD><TD>1-Mar-24</TD><TD>&nbsp;</TD></TR><TR><TD>19</TD><TD>1-Apr-24</TD><TD>10541</TD><TD>Idrees</TD><TD>45555887781</TD><TD>25-Mar-24</TD><TD>He was hired in March 2024 but was included in April 2024 payroll. I want him to be counted in the month of March 2024 only as per hiring date</TD></TR><TR><TD>20</TD><TD>1-Apr-24</TD><TD>10557</TD><TD>Waseem</TD><TD>45555887776</TD><TD>1-Apr-24</TD><TD>Confirmed with new Emp ID</TD></TR><TR><TD>21</TD><TD>1-Apr-24</TD><TD>10558</TD><TD>khurram</TD><TD>45555887777</TD><TD>1-Apr-24</TD><TD>Confirmed with new Emp ID</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>1- Imtiaz and safdar joined in Jan 2024 and confirmed in Mar 2024 with a new emp ID, CNIC is the same. I do not want to count them in March 2024 because they were already counted in Jan 2024. <STRONG><EM>(That i have already done with DAX with the following)</EM></STRONG></P><P>Step 1: Add a calculated column to determine the first hiring date for each CNIC:</P><P>firsthiringdate =</P><P>CALCULATE(</P><P>&nbsp; &nbsp; MIN(Payroll[Hiring Date]),</P><P>&nbsp; &nbsp; ALLEXCEPT(Payroll,Payroll[CNIC]))</P><P><BR /><BR />Step 2: Create a measure to count the number of unique employees who joined in each month based on this first hiring date</P><P>VAR CurrentMonth = SELECTEDVALUE(Payroll[Payroll Month])</P><P>RETURN</P><P>CALCULATE(</P><P>&nbsp; &nbsp; DISTINCTCOUNT(Payroll[CNIC]),</P><P>&nbsp; &nbsp; FILTER(</P><P>&nbsp; &nbsp; &nbsp; &nbsp; Payroll,</P><P>&nbsp; &nbsp; &nbsp; &nbsp; Payroll[firsthiringdate] &gt;= EOMONTH(CurrentMonth, -1) +1 &amp;&amp;</P><P>&nbsp; &nbsp; &nbsp; &nbsp; Payroll[firsthiringdate] &lt;= EOMONTH(CurrentMonth,1)))</P><DIV><DIV><TABLE><TBODY><TR><TD>Months</TD><TD>Result</TD></TR><TR><TD>Jan&nbsp;</TD><TD>2</TD></TR><TR><TD>Feb</TD><TD>2</TD></TR><TR><TD>Mar</TD><TD>1</TD></TR><TR><TD>Apr</TD><TD>0</TD></TR></TBODY></TABLE><SPAN><BR /><STRONG>2 - However, Mr. idrees who was joined in March 2024 is not being counted since he has been added in the Payroll of April 2024</STRONG></SPAN></DIV><DIV><SPAN><STRONG>I want his count reflects in the month of March 2024 but the with aforemention DAX he is not refelecting in March 2024 neither in April 2024</STRONG><BR /></SPAN><TABLE><TBODY><TR><TD>Months</TD><TD>Result</TD><TD>Required&nbsp;</TD></TR><TR><TD>Jan&nbsp;</TD><TD>2</TD><TD>2</TD></TR><TR><TD>Feb</TD><TD>2</TD><TD>2</TD></TR><TR><TD>Mar</TD><TD>1</TD><TD>2</TD></TR><TR><TD>Apr</TD><TD>0</TD><TD>0</TD></TR></TBODY></TABLE><SPAN><BR />Please help in this regards and suggest the DAX where my both 1 and 2&nbsp; condition gets fulfilled so that i can move forward quickly.<BR />PBX FIle is enclosed for working (<A href="https://drive.google.com/file/d/1N7qHOwNoO9nsP_bkmLxt4keIBanrcxAA/view?usp=sharing" target="_blank" rel="noopener">https://drive.google.com/file/d/1N7qHOwNoO9nsP_bkmLxt4keIBanrcxAA/view?usp=sharing</A> )<BR /></SPAN></DIV><DIV><SPAN><BR />Regards<BR />Saad Hanif<BR /><BR /></SPAN></DIV></DIV> Thu, 06 Jun 2024 10:41:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3976789#M154189 Saad_Hanif456 2024-06-06T10:41:05Z Re: New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979161#M154284 <P>Hi I think below will be your expected out based on data. Feb count will be 3 , Khurram, Arbaas, Waseem</P><P><img /></P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P> Fri, 07 Jun 2024 08:43:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979161#M154284 Rupak_bi 2024-06-07T08:43:56Z Re: New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979573#M154294 <P>Dear <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="583487" data-lia-user-login="Rupak_bi" class="lia-mention lia-mention-user">Rupak_bi</a>,<BR /><BR />I wanted the result same as in the following table visual:<BR /><BR />Note: Have the silcer of calendar[month], calender[year] and department [name] on the canvas<BR /><BR /></P><TABLE><TBODY><TR><TD>Month&nbsp;</TD><TD>Name&nbsp;</TD><TD>Hire</TD></TR><TR><TD>Jan</TD><TD>Imtiaz&nbsp;</TD><TD>1</TD></TR><TR><TD>Jan</TD><TD>Hussain</TD><TD>1</TD></TR><TR><TD>Feb&nbsp;</TD><TD>Waseem</TD><TD>1</TD></TR><TR><TD>Feb&nbsp;</TD><TD>Khurram</TD><TD>1</TD></TR><TR><TD>Feb&nbsp;</TD><TD>Arbaas</TD><TD>1</TD></TR><TR><TD>Mar</TD><TD>Maheen</TD><TD>1</TD></TR><TR><TD>Mar</TD><TD>Idress</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Regards,</P><P>Saad Hanif</P> Fri, 07 Jun 2024 11:00:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979573#M154294 Saad_Hanif456 2024-06-07T11:00:01Z Re: New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979737#M154295 <P>just update the table relationship to "firsthiredate"</P><P><img /></P><P><img /></P><P>If this solves your problem, Plz accept it as solution.</P><P>&nbsp;</P> Fri, 07 Jun 2024 11:53:25 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3979737#M154295 Rupak_bi 2024-06-07T11:53:25Z Re: New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3982827#M154421 <P>Dear <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="583487" data-lia-user-login="Rupak_bi" class="lia-mention lia-mention-user">Rupak_bi</a> ,</P><P>&nbsp;</P><P>Thank you so much</P> Mon, 10 Jun 2024 07:36:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3982827#M154421 Saad_Hanif456 2024-06-10T07:36:00Z Re: New Joiners Count https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3982886#M154425 Hi , Thanks for your reply. Please accept my last reply as solution so that other fellow can refer. Mon, 10 Jun 2024 08:04:23 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/New-Joiners-Count/m-p/3982886#M154425 Rupak_bi 2024-06-10T08:04:23Z