topic Re: DAX Sumifs Equivalent but with Criteria based on Another Column in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3889586#M151706 <P>It works!! Thank you so much!! I have marked this as the solution already <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Fri, 03 May 2024 15:35:39 GMT superhayan 2024-05-03T15:35:39Z DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882382#M151526 <P>Hello all, I have problem building a DAX measure equivalent to Sumifs in excel. To start with, I know it is to use CALCULATE(SUM(),filter 1, filter 2) but I don't know how to input the filter when it is based on another column.</P><P>&nbsp;</P><P>Below is a simplified version of my data - monthly revenue records from a list of clients from 2021-2024. As some are new clients and some are churned, not all the clients have records in the whole period.</P><TABLE><TBODY><TR><TD><STRONG>Client</STRONG></TD><TD><STRONG>Revenue Month</STRONG></TD><TD><STRONG>Gross Revenue</STRONG></TD></TR><TR><TD>abc</TD><TD>01 September 2023</TD><TD>2049</TD></TR><TR><TD>abc</TD><TD>01 January 2024</TD><TD>29</TD></TR><TR><TD>qwe</TD><TD>01 December 2023</TD><TD>2</TD></TR><TR><TD>qwe</TD><TD>01 October 2023</TD><TD>810</TD></TR><TR><TD>qwe</TD><TD>01 September 2023</TD><TD>7665</TD></TR><TR><TD>asd</TD><TD>01 December 2021</TD><TD>576</TD></TR><TR><TD>asd</TD><TD>01 November 2022</TD><TD>470</TD></TR><TR><TD>asd</TD><TD>01 October 2022</TD><TD>859</TD></TR><TR><TD>asd</TD><TD>01 September 2022</TD><TD>1371</TD></TR><TR><TD>bnm</TD><TD>01 August 2022</TD><TD>672</TD></TR><TR><TD>yui</TD><TD>01 July 2022</TD><TD>43</TD></TR><TR><TD>yui</TD><TD>01 September 2023</TD><TD>200</TD></TR><TR><TD>yui</TD><TD>01 January 2024</TD><TD>1161</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The SUMIFS I would like to have is to sum up the Revenue amount with below 2 criteria.&nbsp;</P><P>1) Current 24 months of revenue</P><P>2) Only from clients that have generated revenue from the same month last year</P><P>&nbsp;</P><P>The final output will be a bar chart of Revenue against Month like below:</P><P><img /></P><P>&nbsp;</P><P>In such case, the Jan 2024 bar will show the sum of last 24 months revenue generated by only the clients that generated revenue in Jan 2023; and the Jul 2023 bar will show the sum of last 24 months revenue generated by only the clients that have generated revenue in Jul 2022.</P><P>&nbsp;</P><P>The underlying data will be updated once a month when new month comes in. I managed to fulfill the first criteria with below measure but don't know how to input the second criteria, becuase I am not filtering the date directly but the client list based ont he date. Please could anyone help?</P><DIV><DIV><SPAN>= </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>([Gross Revenue]), </SPAN><SPAN>DATESINPERIOD</SPAN><SPAN>([Revenue Month], </SPAN><SPAN>MAX</SPAN><SPAN>([Revenue Month]),-</SPAN><SPAN>24</SPAN><SPAN>, MONTH))</SPAN></DIV></DIV><P>&nbsp;</P><P>Thanks a lot!!!</P> Wed, 01 May 2024 11:16:25 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882382#M151526 superhayan 2024-05-01T11:16:25Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882679#M151531 <P>Your sample data is insufficient.</P> <P>&nbsp;</P> <P><img /></P> <P>Please provide sample data <STRONG>that fully covers your issue</STRONG>.<BR />Please show the expected outcome based on the sample data you provided.</P> Wed, 01 May 2024 12:48:20 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882679#M151531 lbendlin 2024-05-01T12:48:20Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882699#M151532 <P>Hello, my data has more than 50k rows to cover all the years so the above data is just a simplified version to show how it looks like. How can I provide this data here?</P><P>&nbsp;</P><P>The bar chart I posted in the question is the expected outcome. Please let me know what else I should provide, thank you.</P> Wed, 01 May 2024 12:56:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882699#M151532 superhayan 2024-05-01T12:56:14Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882705#M151533 <P>none of your sample data matches your criteria.&nbsp;Please provide sample data <STRONG>that fully covers your issue</STRONG>.<BR /><BR /></P> Wed, 01 May 2024 12:57:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3882705#M151533 lbendlin 2024-05-01T12:57:32Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883187#M151545 <P>Sorry for that. I have created another sample data as below with 4 clients and their revenue spread across 2021-2024 (not all the months have revenue from all 4 clients). Would that be fine? Thanks a lot!!<BR /><BR /></P><TABLE><TBODY><TR><TD>Client</TD><TD>Revenue Month</TD><TD>Gross Revenue</TD></TR><TR><TD>abc</TD><TD>01 January 2021</TD><TD>9293</TD></TR><TR><TD>abc</TD><TD>01 February 2021</TD><TD>8766</TD></TR><TR><TD>abc</TD><TD>01 March 2021</TD><TD>4686</TD></TR><TR><TD>abc</TD><TD>01 April 2021</TD><TD>4359</TD></TR><TR><TD>abc</TD><TD>01 May 2021</TD><TD>7045</TD></TR><TR><TD>abc</TD><TD>01 June 2021</TD><TD>2887</TD></TR><TR><TD>abc</TD><TD>01 July 2021</TD><TD>1980</TD></TR><TR><TD>abc</TD><TD>01 August 2021</TD><TD>7095</TD></TR><TR><TD>abc</TD><TD>01 September 2021</TD><TD>1090</TD></TR><TR><TD>abc</TD><TD>01 October 2021</TD><TD>4097</TD></TR><TR><TD>abc</TD><TD>01 November 2021</TD><TD>1025</TD></TR><TR><TD>abc</TD><TD>01 December 2021</TD><TD>5564</TD></TR><TR><TD>abc</TD><TD>01 January 2022</TD><TD>9342</TD></TR><TR><TD>abc</TD><TD>01 February 2022</TD><TD>7857</TD></TR><TR><TD>abc</TD><TD>01 March 2022</TD><TD>4326</TD></TR><TR><TD>abc</TD><TD>01 April 2022</TD><TD>1661</TD></TR><TR><TD>abc</TD><TD>01 May 2022</TD><TD>568</TD></TR><TR><TD>abc</TD><TD>01 June 2022</TD><TD>7381</TD></TR><TR><TD>abc</TD><TD>01 July 2022</TD><TD>1044</TD></TR><TR><TD>abc</TD><TD>01 August 2022</TD><TD>9313</TD></TR><TR><TD>abc</TD><TD>01 September 2022</TD><TD>3289</TD></TR><TR><TD>abc</TD><TD>01 October 2022</TD><TD>2524</TD></TR><TR><TD>abc</TD><TD>01 November 2022</TD><TD>8067</TD></TR><TR><TD>abc</TD><TD>01 December 2022</TD><TD>5067</TD></TR><TR><TD>abc</TD><TD>01 January 2023</TD><TD>653</TD></TR><TR><TD>abc</TD><TD>01 February 2023</TD><TD>2506</TD></TR><TR><TD>abc</TD><TD>01 March 2023</TD><TD>7750</TD></TR><TR><TD>abc</TD><TD>01 April 2023</TD><TD>6728</TD></TR><TR><TD>abc</TD><TD>01 May 2023</TD><TD>1409</TD></TR><TR><TD>abc</TD><TD>01 June 2023</TD><TD>1119</TD></TR><TR><TD>abc</TD><TD>01 July 2023</TD><TD>7218</TD></TR><TR><TD>abc</TD><TD>01 August 2023</TD><TD>1080</TD></TR><TR><TD>abc</TD><TD>01 September 2023</TD><TD>7984</TD></TR><TR><TD>abc</TD><TD>01 October 2023</TD><TD>4474</TD></TR><TR><TD>abc</TD><TD>01 November 2023</TD><TD>8752</TD></TR><TR><TD>abc</TD><TD>01 December 2023</TD><TD>7937</TD></TR><TR><TD>abc</TD><TD>01 January 2024</TD><TD>6201</TD></TR><TR><TD>abc</TD><TD>01 February 2024</TD><TD>8056</TD></TR><TR><TD>abc</TD><TD>01 March 2024</TD><TD>8611</TD></TR><TR><TD>abc</TD><TD>01 April 2024</TD><TD>1857</TD></TR><TR><TD>abc</TD><TD>01 May 2024</TD><TD>7792</TD></TR><TR><TD>abc</TD><TD>01 June 2024</TD><TD>9599</TD></TR><TR><TD>abc</TD><TD>01 July 2024</TD><TD>844</TD></TR><TR><TD>abc</TD><TD>01 August 2024</TD><TD>7523</TD></TR><TR><TD>abc</TD><TD>01 September 2024</TD><TD>2393</TD></TR><TR><TD>abc</TD><TD>01 October 2024</TD><TD>3873</TD></TR><TR><TD>abc</TD><TD>01 November 2024</TD><TD>8115</TD></TR><TR><TD>abc</TD><TD>01 December 2024</TD><TD>3921</TD></TR><TR><TD>def</TD><TD>01 January 2023</TD><TD>3446</TD></TR><TR><TD>def</TD><TD>01 February 2023</TD><TD>688</TD></TR><TR><TD>def</TD><TD>01 March 2023</TD><TD>2387</TD></TR><TR><TD>def</TD><TD>01 April 2023</TD><TD>7429</TD></TR><TR><TD>def</TD><TD>01 May 2023</TD><TD>8737</TD></TR><TR><TD>def</TD><TD>01 June 2023</TD><TD>6088</TD></TR><TR><TD>def</TD><TD>01 July 2023</TD><TD>9206</TD></TR><TR><TD>def</TD><TD>01 August 2023</TD><TD>5973</TD></TR><TR><TD>def</TD><TD>01 September 2023</TD><TD>4737</TD></TR><TR><TD>def</TD><TD>01 October 2023</TD><TD>4722</TD></TR><TR><TD>def</TD><TD>01 November 2023</TD><TD>3005</TD></TR><TR><TD>def</TD><TD>01 December 2023</TD><TD>5171</TD></TR><TR><TD>ghi</TD><TD>01 November 2022</TD><TD>2255</TD></TR><TR><TD>ghi</TD><TD>01 December 2022</TD><TD>2052</TD></TR><TR><TD>ghi</TD><TD>01 January 2023</TD><TD>3458</TD></TR><TR><TD>ghi</TD><TD>01 February 2023</TD><TD>9694</TD></TR><TR><TD>ghi</TD><TD>01 March 2023</TD><TD>5958</TD></TR><TR><TD>ghi</TD><TD>01 April 2023</TD><TD>2755</TD></TR><TR><TD>ghi</TD><TD>01 May 2023</TD><TD>2830</TD></TR><TR><TD>ghi</TD><TD>01 June 2023</TD><TD>6555</TD></TR><TR><TD>ghi</TD><TD>01 July 2023</TD><TD>1280</TD></TR><TR><TD>ghi</TD><TD>01 August 2023</TD><TD>2806</TD></TR><TR><TD>ghi</TD><TD>01 September 2023</TD><TD>6985</TD></TR><TR><TD>ghi</TD><TD>01 October 2023</TD><TD>5588</TD></TR><TR><TD>ghi</TD><TD>01 November 2023</TD><TD>4413</TD></TR><TR><TD>ghi</TD><TD>01 December 2023</TD><TD>9755</TD></TR><TR><TD>ghi</TD><TD>01 January 2024</TD><TD>3674</TD></TR><TR><TD>ghi</TD><TD>01 February 2024</TD><TD>4631</TD></TR><TR><TD>ghi</TD><TD>01 March 2024</TD><TD>8251</TD></TR><TR><TD>ghi</TD><TD>01 April 2024</TD><TD>1665</TD></TR><TR><TD>ghi</TD><TD>01 May 2024</TD><TD>3965</TD></TR><TR><TD>ghi</TD><TD>01 June 2024</TD><TD>5606</TD></TR><TR><TD>ghi</TD><TD>01 July 2024</TD><TD>5114</TD></TR><TR><TD>ghi</TD><TD>01 August 2024</TD><TD>3951</TD></TR><TR><TD>ghi</TD><TD>01 September 2024</TD><TD>4773</TD></TR><TR><TD>ghi</TD><TD>01 October 2024</TD><TD>9556</TD></TR><TR><TD>ghi</TD><TD>01 November 2024</TD><TD>3399</TD></TR><TR><TD>ghi</TD><TD>01 December 2024</TD><TD>7994</TD></TR><TR><TD>jkl</TD><TD>01 April 2023</TD><TD>6032</TD></TR><TR><TD>jkl</TD><TD>01 May 2023</TD><TD>9406</TD></TR><TR><TD>jkl</TD><TD>01 June 2023</TD><TD>3464</TD></TR><TR><TD>jkl</TD><TD>01 July 2023</TD><TD>3598</TD></TR><TR><TD>jkl</TD><TD>01 August 2023</TD><TD>4894</TD></TR><TR><TD>jkl</TD><TD>01 September 2023</TD><TD>5268</TD></TR><TR><TD>jkl</TD><TD>01 October 2023</TD><TD>9846</TD></TR><TR><TD>jkl</TD><TD>01 November 2023</TD><TD>3213</TD></TR><TR><TD>jkl</TD><TD>01 December 2023</TD><TD>5506</TD></TR><TR><TD>jkl</TD><TD>01 January 2024</TD><TD>6109</TD></TR><TR><TD>jkl</TD><TD>01 February 2024</TD><TD>2709</TD></TR><TR><TD>jkl</TD><TD>01 March 2024</TD><TD>4500</TD></TR><TR><TD>jkl</TD><TD>01 April 2024</TD><TD>4838</TD></TR><TR><TD>jkl</TD><TD>01 May 2024</TD><TD>1039</TD></TR><TR><TD>jkl</TD><TD>01 June 2024</TD><TD>2135</TD></TR><TR><TD>jkl</TD><TD>01 July 2024</TD><TD>3001</TD></TR><TR><TD>jkl</TD><TD>01 August 2024</TD><TD>4263</TD></TR><TR><TD>jkl</TD><TD>01 September 2024</TD><TD>8892</TD></TR><TR><TD>jkl</TD><TD>01 October 2024</TD><TD>2496</TD></TR><TR><TD>jkl</TD><TD>01 November 2024</TD><TD>6810</TD></TR><TR><TD>jkl</TD><TD>01 December 2024</TD><TD>8066</TD></TR></TBODY></TABLE> Wed, 01 May 2024 16:16:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883187#M151545 superhayan 2024-05-01T16:16:56Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883311#M151548 <P><img /></P> <P>&nbsp;</P> <LI-CODE lang="markup">Cumul = var d = max('Calendar'[Date]) var c = values('Table'[Client]) var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client]) var yoyc = intersect(c,pc) return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in yoyc,'Calendar'[Date] &lt;=d,'Calendar'[Date]&gt;EDATE(d,-24))</LI-CODE> <P>&nbsp;</P> Wed, 01 May 2024 17:20:24 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883311#M151548 lbendlin 2024-05-01T17:20:24Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883495#M151549 <P>Thanks very much for your reply with solution but it is not correct unfortunately.&nbsp;</P><P>&nbsp;</P><P>For example, the bar of Dec 2024 should look at the last 24 months revenue generated by the clients who contribute to revenue in Dec 2023. As all 4 clients generate revenue in Dec 2023, that bar should look at the revenue from all 4 clients from Jan 2022 to Dec 2024. The total sum of the Dec 2024 bar should be $418,725. Also, the Cuml chart is missing out Client def (dark blue color) but it should be included in some bars.</P><P>&nbsp;</P><P>Please could you help to take a look again? Thanks so much really!!</P> Wed, 01 May 2024 18:29:52 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883495#M151549 superhayan 2024-05-01T18:29:52Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883588#M151550 <P>change</P> <LI-CODE lang="markup">Cumul = var d = max('Calendar'[Date]) var c = values('Table'[Client]) var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client]) var yoyc = intersect(c,pc) return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in yoyc,'Calendar'[Date] &lt;=d,'Calendar'[Date]&gt;EDATE(d,-24))</LI-CODE> <P>to</P> <LI-CODE lang="markup">Cumul = var d = max('Calendar'[Date]) var pc = SELECTCOLUMNS(CALCULATETABLE(VALUES('Table'[Client]),'Calendar'[Date]=EDATE(d,-12)),"Client",[Client]) return CALCULATE(sum('Table'[Gross Revenue]),'Table'[Client] in pc,'Calendar'[Date] &lt;=d,'Calendar'[Date]&gt;EDATE(d,-24))</LI-CODE> Wed, 01 May 2024 19:04:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3883588#M151550 lbendlin 2024-05-01T19:04:03Z Re: DAX Sumifs Equivalent but with Criteria based on Another Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3889586#M151706 <P>It works!! Thank you so much!! I have marked this as the solution already <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Fri, 03 May 2024 15:35:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-Sumifs-Equivalent-but-with-Criteria-based-on-Another-Column/m-p/3889586#M151706 superhayan 2024-05-03T15:35:39Z