topic Re: distinct count of users who have their sales in each month in last six-month in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3826898#M149625 <P>Anonymous</LI-USER>&nbsp;<BR /><BR /><SPAN>Create a new calculated column in your table to extract the month and year from the Date column. You can use the following DAX formula:</SPAN><BR />MonthYear = FORMAT('Table'[Date], "YYYY-MM")<BR /><BR /><SPAN>Next, create a new measure to count the distinct number of users who have sales in each month. You can use the following DAX formula:</SPAN><BR />DistinctUsersLast6Months =<BR />CALCULATE(<BR />DISTINCTCOUNT('Table'[User Code]),<BR />FILTER(<BR />ALL('Table'),<BR />'Table'[MonthYear] &gt;= FORMAT(TODAY() - 6 * 30, "YYYY-MM") &amp;&amp;<BR />'Table'[MonthYear] &lt;= FORMAT(TODAY(), "YYYY-MM") &amp;&amp;<BR />'Table'[Sales Quantity] &gt; 0<BR />)<BR />)<BR /><BR />Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!<BR /><BR /><BR /></P> Wed, 10 Apr 2024 11:03:32 GMT johnbasha33 2024-04-10T11:03:32Z distinct count of users who have their sales in each month in last six-month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3825639#M149588 <P>Dear Community,</P><P>I have 3 years of sales data of users.</P><P>I have need to find the no. of users who have their sales each month in last 6 month.</P><P>Here is the sample data:</P><TABLE><TBODY><TR><TD>User Code</TD><TD>Sales Qunatity</TD><TD>Date</TD></TR><TR><TD>33771</TD><TD>10</TD><TD>12-03-2024</TD></TR><TR><TD>33774</TD><TD>10</TD><TD>11-03-2024</TD></TR><TR><TD>33796</TD><TD>10</TD><TD>28-02-2024</TD></TR><TR><TD>33802</TD><TD>10</TD><TD>09-01-2024</TD></TR><TR><TD>33802</TD><TD>10</TD><TD>13-03-2024</TD></TR><TR><TD>33803</TD><TD>10</TD><TD>27-11-2023</TD></TR><TR><TD>33803</TD><TD>10</TD><TD>13-09-2023</TD></TR><TR><TD>33842</TD><TD>10</TD><TD>28-02-2024</TD></TR><TR><TD>33852</TD><TD>10</TD><TD>01-08-2023</TD></TR><TR><TD>33854</TD><TD>10</TD><TD>18-02-2024</TD></TR><TR><TD>33854</TD><TD>10</TD><TD>25-02-2024</TD></TR><TR><TD>33890</TD><TD>10</TD><TD>14-02-2024</TD></TR><TR><TD>33901</TD><TD>7.5</TD><TD>03-01-2024</TD></TR><TR><TD>33908</TD><TD>7.5</TD><TD>20-10-2023</TD></TR><TR><TD>43868</TD><TD>7.5</TD><TD>16-01-2024</TD></TR><TR><TD>44360</TD><TD>7.5</TD><TD>09-12-2023</TD></TR><TR><TD>33774</TD><TD>7.5</TD><TD>05-10-2023</TD></TR><TR><TD>57557</TD><TD>7.5</TD><TD>23-02-2024</TD></TR><TR><TD>57824</TD><TD>7.5</TD><TD>04-10-2023</TD></TR><TR><TD>58818</TD><TD>7.5</TD><TD>16-02-2024</TD></TR><TR><TD>70852</TD><TD>7.5</TD><TD>13-11-2023</TD></TR><TR><TD>33774</TD><TD>7.5</TD><TD>25-02-2024</TD></TR><TR><TD>33774</TD><TD>9</TD><TD>20-01-2024</TD></TR><TR><TD>3488</TD><TD>11.25</TD><TD>30-09-2023</TD></TR><TR><TD>48268</TD><TD>11.25</TD><TD>10-01-2024</TD></TR><TR><TD>14820</TD><TD>25</TD><TD>28-02-2024</TD></TR><TR><TD>14889</TD><TD>25</TD><TD>05-10-2023</TD></TR><TR><TD>14890</TD><TD>25</TD><TD>27-02-2024</TD></TR><TR><TD>3312</TD><TD>25</TD><TD>10-02-2024</TD></TR><TR><TD>33599</TD><TD>25</TD><TD>09-01-2024</TD></TR><TR><TD>33774</TD><TD>25</TD><TD>29-09-2023</TD></TR><TR><TD>33774</TD><TD>25</TD><TD>06-03-2024</TD></TR><TR><TD>33790</TD><TD>25</TD><TD>12-03-2024</TD></TR><TR><TD>33796</TD><TD>7.5</TD><TD>04-02-2024</TD></TR><TR><TD>33796</TD><TD>7.5</TD><TD>24-01-2024</TD></TR><TR><TD>33774</TD><TD>11.25</TD><TD>20-10-2023</TD></TR><TR><TD>33812</TD><TD>11.25</TD><TD>30-11-2023</TD></TR><TR><TD>33838</TD><TD>10</TD><TD>08-11-2023</TD></TR><TR><TD>33841</TD><TD>10</TD><TD>06-11-2023</TD></TR><TR><TD>33871</TD><TD>10</TD><TD>27-02-2024</TD></TR><TR><TD>33871</TD><TD>10</TD><TD>22-01-2024</TD></TR><TR><TD>33890</TD><TD>10</TD><TD>18-08-2023</TD></TR><TR><TD>33908</TD><TD>7.5</TD><TD>18-12-2023</TD></TR><TR><TD>33908</TD><TD>7.5</TD><TD>12-11-2023</TD></TR><TR><TD>33915</TD><TD>7.5</TD><TD>31-12-2023</TD></TR><TR><TD>33927</TD><TD>7.5</TD><TD>15-01-2024</TD></TR><TR><TD>33927</TD><TD>8</TD><TD>30-11-2023</TD></TR><TR><TD>33929</TD><TD>8</TD><TD>02-01-2024</TD></TR><TR><TD>33948</TD><TD>11</TD><TD>06-03-2024</TD></TR><TR><TD>33948</TD><TD>14</TD><TD>13-01-2024</TD></TR><TR><TD>33964</TD><TD>50</TD><TD>13-01-2024</TD></TR><TR><TD>33771</TD><TD>19</TD><TD>12-03-2024</TD></TR><TR><TD>33802</TD><TD>19</TD><TD>10-02-2024</TD></TR><TR><TD>33803</TD><TD>19</TD><TD>06-03-2024</TD></TR><TR><TD>33803</TD><TD>19</TD><TD>30-09-2023</TD></TR><TR><TD>33842</TD><TD>19</TD><TD>14-10-2023</TD></TR><TR><TD>33852</TD><TD>19</TD><TD>28-02-2024</TD></TR><TR><TD>33854</TD><TD>19</TD><TD>04-10-2023</TD></TR><TR><TD>33854</TD><TD>19</TD><TD>16-02-2024</TD></TR><TR><TD>33854</TD><TD>19</TD><TD>19-11-2023</TD></TR><TR><TD>33890</TD><TD>19</TD><TD>12-03-2024</TD></TR><TR><TD>33892</TD><TD>19</TD><TD>14-12-2023</TD></TR><TR><TD>33774</TD><TD>19</TD><TD>30-12-2023</TD></TR><TR><TD>33908</TD><TD>19</TD><TD>03-11-2023</TD></TR><TR><TD>33908</TD><TD>19</TD><TD>11-02-2024</TD></TR><TR><TD>33914</TD><TD>19</TD><TD>06-02-2024</TD></TR><TR><TD>33915</TD><TD>19</TD><TD>23-12-2023</TD></TR><TR><TD>44360</TD><TD>19.95</TD><TD>26-12-2023</TD></TR><TR><TD>70852</TD><TD>19.95</TD><TD>07-10-2023</TD></TR><TR><TD>33774</TD><TD>19.95</TD><TD>03-11-2023</TD></TR><TR><TD>3488</TD><TD>19.95</TD><TD>09-08-2023</TD></TR><TR><TD>3488</TD><TD>19.95</TD><TD>18-12-2023</TD></TR><TR><TD>48268</TD><TD>19.95</TD><TD>12-12-2023</TD></TR><TR><TD>14782</TD><TD>19.95</TD><TD>16-01-2024</TD></TR><TR><TD>14782</TD><TD>19.95</TD><TD>25-08-2023</TD></TR><TR><TD>14820</TD><TD>19.95</TD><TD>13-02-2024</TD></TR><TR><TD>14889</TD><TD>19.95</TD><TD>08-02-2024</TD></TR><TR><TD>14890</TD><TD>22.05</TD><TD>26-12-2023</TD></TR><TR><TD>3312</TD><TD>22.05</TD><TD>30-11-2023</TD></TR><TR><TD>33774</TD><TD>22.05</TD><TD>30-11-2023</TD></TR><TR><TD>33803</TD><TD>23.1</TD><TD>13-09-2023</TD></TR><TR><TD>33812</TD><TD>23.1</TD><TD>12-11-2023</TD></TR><TR><TD>33841</TD><TD>23.1</TD><TD>31-12-2023</TD></TR><TR><TD>33871</TD><TD>23.1</TD><TD>15-01-2024</TD></TR><TR><TD>33871</TD><TD>23.1</TD><TD>12-01-2024</TD></TR><TR><TD>33901</TD><TD>23.1</TD><TD>30-10-2023</TD></TR><TR><TD>33901</TD><TD>23.1</TD><TD>12-03-2024</TD></TR><TR><TD>33908</TD><TD>23.1</TD><TD>11-02-2024</TD></TR><TR><TD>33915</TD><TD>23.1</TD><TD>05-11-2023</TD></TR><TR><TD>33915</TD><TD>23.1</TD><TD>09-03-2024</TD></TR><TR><TD>33774</TD><TD>23.1</TD><TD>09-12-2023</TD></TR><TR><TD>33927</TD><TD>23.1</TD><TD>08-01-2024</TD></TR><TR><TD>33929</TD><TD>23.1</TD><TD>09-11-2023</TD></TR><TR><TD>33948</TD><TD>23.1</TD><TD>02-03-2024</TD></TR><TR><TD>33948</TD><TD>23.1</TD><TD>31-12-2023</TD></TR><TR><TD>33948</TD><TD>23.1</TD><TD>23-10-2023</TD></TR><TR><TD>33984</TD><TD>23.1</TD><TD>06-09-2023</TD></TR><TR><TD>34013</TD><TD>23.1</TD><TD>27-02-2024</TD></TR><TR><TD>42739</TD><TD>23.1</TD><TD>05-03-2024</TD></TR><TR><TD>45148</TD><TD>23.1</TD><TD>14-02-2024</TD></TR><TR><TD>58092</TD><TD>23.1</TD><TD>09-12-2023</TD></TR><TR><TD>66212</TD><TD>23.1</TD><TD>01-11-2023</TD></TR><TR><TD>66217</TD><TD>23.1</TD><TD>01-03-2024</TD></TR><TR><TD>55243</TD><TD>24.15</TD><TD>25-01-2024</TD></TR><TR><TD>121955</TD><TD>25.2</TD><TD>12-08-2023</TD></TR><TR><TD>14890</TD><TD>26.25</TD><TD>08-11-2023</TD></TR><TR><TD>3336</TD><TD>26.25</TD><TD>28-02-2024</TD></TR><TR><TD>3346</TD><TD>26.25</TD><TD>04-10-2023</TD></TR><TR><TD>33599</TD><TD>26.25</TD><TD>11-01-2024</TD></TR><TR><TD>3367</TD><TD>26.25</TD><TD>05-11-2023</TD></TR><TR><TD>3404</TD><TD>26.25</TD><TD>12-03-2024</TD></TR><TR><TD>3446</TD><TD>26.25</TD><TD>18-02-2024</TD></TR><TR><TD>3489</TD><TD>26.25</TD><TD>13-11-2023</TD></TR><TR><TD>43056</TD><TD>26.25</TD><TD>09-02-2024</TD></TR><TR><TD>43868</TD><TD>26.25</TD><TD>02-02-2024</TD></TR><TR><TD>44342</TD><TD>26.25</TD><TD>02-02-2024</TD></TR><TR><TD>44360</TD><TD>26.25</TD><TD>08-09-2023</TD></TR><TR><TD>33796</TD><TD>26.25</TD><TD>27-02-2024</TD></TR><TR><TD>33802</TD><TD>26.25</TD><TD>07-03-2024</TD></TR><TR><TD>33803</TD><TD>26.25</TD><TD>14-12-2023</TD></TR><TR><TD>33812</TD><TD>26.25</TD><TD>09-02-2024</TD></TR><TR><TD>33812</TD><TD>26.25</TD><TD>31-08-2023</TD></TR></TBODY></TABLE><P>and this is the no. of users that i need to find based on sample data in power bi&nbsp;</P><P><img /></P><P>Thanks!</P> Wed, 10 Apr 2024 05:31:26 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3825639#M149588 Anonymous 2024-04-10T05:31:26Z Re: distinct count of users who have their sales in each month in last six-month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3826898#M149625 <P>Anonymous</LI-USER>&nbsp;<BR /><BR /><SPAN>Create a new calculated column in your table to extract the month and year from the Date column. You can use the following DAX formula:</SPAN><BR />MonthYear = FORMAT('Table'[Date], "YYYY-MM")<BR /><BR /><SPAN>Next, create a new measure to count the distinct number of users who have sales in each month. You can use the following DAX formula:</SPAN><BR />DistinctUsersLast6Months =<BR />CALCULATE(<BR />DISTINCTCOUNT('Table'[User Code]),<BR />FILTER(<BR />ALL('Table'),<BR />'Table'[MonthYear] &gt;= FORMAT(TODAY() - 6 * 30, "YYYY-MM") &amp;&amp;<BR />'Table'[MonthYear] &lt;= FORMAT(TODAY(), "YYYY-MM") &amp;&amp;<BR />'Table'[Sales Quantity] &gt; 0<BR />)<BR />)<BR /><BR />Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!<BR /><BR /><BR /></P> Wed, 10 Apr 2024 11:03:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3826898#M149625 johnbasha33 2024-04-10T11:03:32Z Re: distinct count of users who have their sales in each month in last six-month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3830164#M149754 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;,</P> <P>&nbsp;</P> <P>Thanks&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="585150" data-lia-user-login="johnbasha33" class="lia-mention lia-mention-user">johnbasha33</a>&nbsp; for the quick reply!</P> <P>(1) This is my test data.&nbsp;&nbsp;</P> <P><img /></P> <P>(2) We can create measures.</P> <LI-CODE lang="markup">Measure = VAR A= SUMMARIZE(ADDCOLUMNS(CALENDAR(EOMONTH(TODAY(),-5),TODAY()),"Format",FORMAT([Date],"YYYY-MM")),[Format]) VAR B=ADDCOLUMNS(A,"TEST",CONCATENATEX(FILTER(ALLSELECTED('Table'),FORMAT([Date],"YYYY-MM") =EARLIER([Format])),[User Code],",")) RETURN COUNTROWS(FILTER(B,CONTAINSSTRING([TEST],MAX('Table'[User Code]))))</LI-CODE><LI-CODE lang="markup">DistinctUsersLast6Months = CALCULATE(DISTINCTCOUNT('Table'[User Code]),FILTER('Table',[Measure]=6))</LI-CODE> <P>(3) Then the result is as follows.</P> <P><img /></P> <P>Best Regards,</P> <P>Neeko Tang</P> <P>If this post  <STRONG><EM>helps</EM></STRONG>, then please consider <STRONG><EM>Accept it as the solution </EM></STRONG> to help the other members find it more quickly.&nbsp;</P> Thu, 11 Apr 2024 08:59:28 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/distinct-count-of-users-who-have-their-sales-in-each-month-in/m-p/3830164#M149754 Anonymous 2024-04-11T08:59:28Z