topic Count after comparing dates from distinct tables in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3853598#M150561 <P>Hello,</P><P>I need help with the following scenario.<BR /><BR />I have a table where&nbsp; sales representatives' training information exists:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Training</TD><TD>Date</TD></TR><TR><TD>A</TD><TD>X</TD><TD>12/1/2024</TD></TR><TR><TD>A</TD><TD>Y</TD><TD>15/2/2024</TD></TR><TR><TD>A</TD><TD>Z</TD><TD>17/3/2024</TD></TR><TR><TD>B</TD><TD>X</TD><TD>&nbsp;</TD></TR><TR><TD>B</TD><TD>Y</TD><TD>12/2/2024</TD></TR><TR><TD>B</TD><TD>Z</TD><TD>&nbsp;</TD></TR><TR><TD>C</TD><TD>X</TD><TD>&nbsp;</TD></TR><TR><TD>C</TD><TD>Y</TD><TD>2/2/2024</TD></TR><TR><TD>D</TD><TD>X</TD><TD>3/3/2024</TD></TR><TR><TD>D</TD><TD>Y</TD><TD>2/3/2024</TD></TR><TR><TD>D</TD><TD>Z</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>And there is also a table that shows the transactions done by each sales rep:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Transaction ID</TD><TD>Transaction Date</TD></TR><TR><TD>A</TD><TD>1</TD><TD>2/1/2024</TD></TR><TR><TD>A</TD><TD>2</TD><TD>12/1/2024</TD></TR><TR><TD>A</TD><TD>3</TD><TD>16/2/2024</TD></TR><TR><TD>B</TD><TD>4</TD><TD>17/3/2024</TD></TR><TR><TD>B</TD><TD>5</TD><TD>1/2/2024</TD></TR><TR><TD>B</TD><TD>6</TD><TD>2/3/2024</TD></TR><TR><TD>C</TD><TD>7</TD><TD>2/4/2024</TD></TR><TR><TD>C</TD><TD>8</TD><TD>3/1/2024</TD></TR><TR><TD>D</TD><TD>9</TD><TD>11/1/2024</TD></TR><TR><TD>D</TD><TD>10</TD><TD>9/1/2024</TD></TR><TR><TD>D</TD><TD>11</TD><TD>3/4/2024</TD></TR><TR><TD>E</TD><TD>12</TD><TD>13/3/2024</TD></TR><TR><TD>E</TD><TD>13</TD><TD>16/3/2024</TD></TR><TR><TD>E</TD><TD>14</TD><TD>5/3/2024</TD></TR></TBODY></TABLE><P><BR /><BR />I need to evaluate sales reps' performance after the trainings&nbsp; by calculating the count of Transaction ids considering that Transaction Date should be after or equal to the Training Date.<BR /><BR />The output should be something like:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Training</TD><TD>Transaction count after training</TD><TD>Transaction Date</TD></TR><TR><TD>A</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">12/1/2024</FONT></TD></TR><TR><TD>A</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">16/2/2024</FONT></TD></TR><TR><TD>D</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">3/4/2024</FONT></TD></TR><TR><TD>A</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">16/2/2024</FONT></TD></TR><TR><TD>B</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">17/3/2024</FONT></TD></TR><TR><TD>C</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">2/4/2024</FONT></TD></TR><TR><TD>D</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">3/4/2024</FONT></TD></TR><TR><TD>A</TD><TD>Z</TD><TD><FONT color="#FF0000">0</FONT></TD><TD>&nbsp;</TD></TR><TR><TD>B</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">2/3/2024</FONT></TD></TR></TBODY></TABLE><P><BR />Which I can then use to assess the training metadata like this:<BR /><BR /></P><TABLE><TBODY><TR><TD>Training</TD><TD>Transaction month</TD><TD>Transaction count after training</TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Jan-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Feb-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Apr-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Feb-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Mar-24</FONT></TD><TD><FONT color="#FF0000">2</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Apr-24</FONT></TD><TD><FONT color="#FF0000">2</FONT></TD></TR><TR><TD>Z</TD><TD>&nbsp;</TD><TD><FONT color="#FF0000">0</FONT></TD></TR></TBODY></TABLE><P><BR />Any idea how this can be achieved? I need to do this in DAX as the tables are very large and cannot do more transformations in PQ.<BR />Thanks a lot for any help.</P> Fri, 19 Apr 2024 14:25:40 GMT Chris2016 2024-04-19T14:25:40Z Count after comparing dates from distinct tables https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3853598#M150561 <P>Hello,</P><P>I need help with the following scenario.<BR /><BR />I have a table where&nbsp; sales representatives' training information exists:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Training</TD><TD>Date</TD></TR><TR><TD>A</TD><TD>X</TD><TD>12/1/2024</TD></TR><TR><TD>A</TD><TD>Y</TD><TD>15/2/2024</TD></TR><TR><TD>A</TD><TD>Z</TD><TD>17/3/2024</TD></TR><TR><TD>B</TD><TD>X</TD><TD>&nbsp;</TD></TR><TR><TD>B</TD><TD>Y</TD><TD>12/2/2024</TD></TR><TR><TD>B</TD><TD>Z</TD><TD>&nbsp;</TD></TR><TR><TD>C</TD><TD>X</TD><TD>&nbsp;</TD></TR><TR><TD>C</TD><TD>Y</TD><TD>2/2/2024</TD></TR><TR><TD>D</TD><TD>X</TD><TD>3/3/2024</TD></TR><TR><TD>D</TD><TD>Y</TD><TD>2/3/2024</TD></TR><TR><TD>D</TD><TD>Z</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>And there is also a table that shows the transactions done by each sales rep:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Transaction ID</TD><TD>Transaction Date</TD></TR><TR><TD>A</TD><TD>1</TD><TD>2/1/2024</TD></TR><TR><TD>A</TD><TD>2</TD><TD>12/1/2024</TD></TR><TR><TD>A</TD><TD>3</TD><TD>16/2/2024</TD></TR><TR><TD>B</TD><TD>4</TD><TD>17/3/2024</TD></TR><TR><TD>B</TD><TD>5</TD><TD>1/2/2024</TD></TR><TR><TD>B</TD><TD>6</TD><TD>2/3/2024</TD></TR><TR><TD>C</TD><TD>7</TD><TD>2/4/2024</TD></TR><TR><TD>C</TD><TD>8</TD><TD>3/1/2024</TD></TR><TR><TD>D</TD><TD>9</TD><TD>11/1/2024</TD></TR><TR><TD>D</TD><TD>10</TD><TD>9/1/2024</TD></TR><TR><TD>D</TD><TD>11</TD><TD>3/4/2024</TD></TR><TR><TD>E</TD><TD>12</TD><TD>13/3/2024</TD></TR><TR><TD>E</TD><TD>13</TD><TD>16/3/2024</TD></TR><TR><TD>E</TD><TD>14</TD><TD>5/3/2024</TD></TR></TBODY></TABLE><P><BR /><BR />I need to evaluate sales reps' performance after the trainings&nbsp; by calculating the count of Transaction ids considering that Transaction Date should be after or equal to the Training Date.<BR /><BR />The output should be something like:</P><TABLE><TBODY><TR><TD>Sales Rep</TD><TD>Training</TD><TD>Transaction count after training</TD><TD>Transaction Date</TD></TR><TR><TD>A</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">12/1/2024</FONT></TD></TR><TR><TD>A</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">16/2/2024</FONT></TD></TR><TR><TD>D</TD><TD>X</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">3/4/2024</FONT></TD></TR><TR><TD>A</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">16/2/2024</FONT></TD></TR><TR><TD>B</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">17/3/2024</FONT></TD></TR><TR><TD>C</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">2/4/2024</FONT></TD></TR><TR><TD>D</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">3/4/2024</FONT></TD></TR><TR><TD>A</TD><TD>Z</TD><TD><FONT color="#FF0000">0</FONT></TD><TD>&nbsp;</TD></TR><TR><TD>B</TD><TD>Y</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">2/3/2024</FONT></TD></TR></TBODY></TABLE><P><BR />Which I can then use to assess the training metadata like this:<BR /><BR /></P><TABLE><TBODY><TR><TD>Training</TD><TD>Transaction month</TD><TD>Transaction count after training</TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Jan-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Feb-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>X</TD><TD><FONT color="#FF0000">Apr-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Feb-24</FONT></TD><TD><FONT color="#FF0000">1</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Mar-24</FONT></TD><TD><FONT color="#FF0000">2</FONT></TD></TR><TR><TD>Y</TD><TD><FONT color="#FF0000">Apr-24</FONT></TD><TD><FONT color="#FF0000">2</FONT></TD></TR><TR><TD>Z</TD><TD>&nbsp;</TD><TD><FONT color="#FF0000">0</FONT></TD></TR></TBODY></TABLE><P><BR />Any idea how this can be achieved? I need to do this in DAX as the tables are very large and cannot do more transformations in PQ.<BR />Thanks a lot for any help.</P> Fri, 19 Apr 2024 14:25:40 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3853598#M150561 Chris2016 2024-04-19T14:25:40Z Re: Count after comparing dates from distinct tables https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3856092#M150649 <P>see attached.&nbsp; You still need to invest in a proper data model.</P> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> Sat, 20 Apr 2024 19:47:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3856092#M150649 lbendlin 2024-04-20T19:47:27Z Re: Count after comparing dates from distinct tables https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3861707#M150859 <P>Thanks a lot, this worked!</P><P>&nbsp;</P><P>Best regards!</P> Tue, 23 Apr 2024 09:14:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-after-comparing-dates-from-distinct-tables/m-p/3861707#M150859 Chris2016 2024-04-23T09:14:27Z