topic Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335890#M172171 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="859703" data-lia-user-login="proavinash" class="lia-mention lia-mention-user">proavinash</a>&nbsp;<BR /><BR />Thanks for the reply from&nbsp;Kedar_Pande,&nbsp;ThxAlot&nbsp;and&nbsp;lbendlin. You can use the dax to achieve your need. By the way, note&nbsp;that there seems to be a problem with the MTD and QTD you posted for 1002 and 1003, the data in the following screenshot seems to be the correct result.<BR /><BR /></P> <P><img /><BR />Measure:&nbsp;</P> <LI-CODE lang="markup">SaleMTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear &amp;&amp; MONTH ( _date ) = _todayMonth &amp;&amp; _date &lt;= TODAY (), SUM ( Demand[Net Sale] ), 0 ) RETURN _re SaleQTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear &amp;&amp; QUARTER ( _date ) = _todayQuarter &amp;&amp; _date &lt;= TODAY (), SUM ( Demand[Net Sale] ), 0 ) RETURN _re SaleYTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear, SUM ( Demand[Net Sale] ), 0 ) RETURN _re</LI-CODE> <P>&nbsp;</P> <P style="text-align: justify; text-justify: inter-ideograph;"><SPAN>Best Regards,<BR />Yang</SPAN><SPAN><BR /></SPAN><SPAN>Community Support Team</SPAN></P> <P style="text-align: justify; text-justify: inter-ideograph;">&nbsp;</P> <P style="background: white;"><SPAN>If there is any post </SPAN><STRONG><SPAN>helps</SPAN></STRONG><SPAN>, then please consider </SPAN><STRONG><SPAN>Accept it as the solution</SPAN></STRONG><SPAN>&nbsp;to</SPAN><SPAN>&nbsp;help the other members find it more quickly.<BR />If I misunderstand your needs or you still have problems on it, please feel free to let us know. </SPAN><STRONG><SPAN>Thanks a lot!</SPAN></STRONG></P> <P style="background: white;"><U><SPAN><A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommunity-Blog%2FHow-to-Get-Your-Question-Answered-Quickly%2Fba-p%2F38490&amp;data=05%7C01%7Cv-yaningyang%40microsoft.com%7C326a6d727194478c197008dbefc04904%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638367381365912356%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=7IzyI2%2FMOAR%2FYCjWYSKCZL2VPmT4PsxuWBjdUdMWKzo%3D&amp;reserved=0" target="_blank">How to get your questions answered quickly</A></SPAN></U><SPAN>&nbsp;-- </SPAN><U><SPAN><A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommunity-Blog%2FHow-to-provide-sample-data-in-the-Power-BI-Forum%2Fba-p%2F963216&amp;data=05%7C01%7Cv-yaningyang%40microsoft.com%7C326a6d727194478c197008dbefc04904%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638367381365919045%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=VeafzdwAsrpisD8s261%2FdyOwJ1aCtBXS3bPA6NODOHY%3D&amp;reserved=0" target="_blank">&nbsp;How to provide sample data in the Power BI Forum</A></SPAN></U></P> Thu, 26 Dec 2024 08:24:44 GMT Anonymous 2024-12-26T08:24:44Z How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335475#M172144 <P>How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump other table.</P><P>&nbsp;</P><P><U><STRONG>Sale dump table</STRONG></U></P><P>Customer code&nbsp; &nbsp; Customer Name&nbsp; &nbsp; &nbsp; &nbsp; date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Region&nbsp; &nbsp; &nbsp; zone&nbsp; &nbsp; &nbsp; Qty&nbsp; &nbsp; &nbsp; &nbsp;Sale Net</P><P>1000001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Ramesh&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10-10-2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;North&nbsp; &nbsp; &nbsp; &nbsp;NCR&nbsp; &nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;183642</P><P>2</P><P>3</P><P>4</P><P>and so on</P><P>&nbsp;</P><P><U><STRONG>Customer Table</STRONG></U></P><P>&nbsp;</P><P>Customer Code&nbsp; &nbsp; &nbsp; MTD Sale&nbsp; &nbsp; &nbsp; QTD Sale&nbsp; &nbsp; &nbsp;YTD Sale&nbsp; &nbsp; &nbsp;Q1 Sale&nbsp; &nbsp; &nbsp;Q2 Sale&nbsp; &nbsp;&nbsp;Q3 Sale&nbsp; &nbsp; &nbsp;Q4 Sale</P><P>1000001</P><P>2</P><P>3</P><P>4</P><P>and so on</P><P>&nbsp;</P> Wed, 18 Dec 2024 17:21:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335475#M172144 proavinash 2024-12-18T17:21:22Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335744#M172161 <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, 18 Dec 2024 23:09:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335744#M172161 lbendlin 2024-12-18T23:09:22Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335805#M172165 <P>NEED RESULT CUSTOMER WISE MTD SALE , QTD SALE, YTD SALE, THEN Q1, Q2 , Q3, Q4, LIKE DISPLAY IN CUSTOMER TABLE.</P><P>&nbsp;</P><P>DATABASE TABLE.</P><TABLE><TBODY><TR><TD>Customer Code</TD><TD>Customer Name</TD><TD>Date</TD><TD>Region</TD><TD>Zone</TD><TD>Qty</TD><TD>Net Sale</TD></TR><TR><TD>10000001</TD><TD>Ramesh</TD><TD>10-10-2018</TD><TD>North</TD><TD>NCR</TD><TD>50</TD><TD>454645</TD></TR><TR><TD>10000002</TD><TD>Ram</TD><TD>10-11-2024</TD><TD>East</TD><TD>TN</TD><TD>45</TD><TD>343645</TD></TR><TR><TD>10000003</TD><TD>Shayam</TD><TD>12-19-2024</TD><TD>West</TD><TD>UP East</TD><TD>64</TD><TD>132323</TD></TR><TR><TD>10000004</TD><TD>Kumar Bisvas</TD><TD>10-13-2018</TD><TD>South</TD><TD>AP</TD><TD>35</TD><TD>4545464</TD></TR><TR><TD>10000005</TD><TD>Raju</TD><TD>10-14-2018</TD><TD>North</TD><TD>WB</TD><TD>67</TD><TD>2343432</TD></TR><TR><TD>10000006</TD><TD>Ram Gopal</TD><TD>10-15-2018</TD><TD>East</TD><TD>KTK</TD><TD>45</TD><TD>8735454</TD></TR><TR><TD>10000007</TD><TD>Kamal</TD><TD>10-16-2018</TD><TD>West</TD><TD>RAJ</TD><TD>75</TD><TD>9834738</TD></TR><TR><TD>10000008</TD><TD>Neel Nitin</TD><TD>10-17-2018</TD><TD>South</TD><TD>GUJ</TD><TD>76</TD><TD>763476</TD></TR><TR><TD>10000009</TD><TD>Suraj</TD><TD>10-18-2018</TD><TD>North</TD><TD>MUM</TD><TD>54</TD><TD>334455</TD></TR><TR><TD>10000010</TD><TD>Jyoti</TD><TD>10-19-2018</TD><TD>East</TD><TD>NCR</TD><TD>45</TD><TD>243545</TD></TR><TR><TD>10000006</TD><TD>Ram Gopal</TD><TD>10-15-2018</TD><TD>East</TD><TD>KTK</TD><TD>45</TD><TD>8735454</TD></TR><TR><TD>10000007</TD><TD>Kamal</TD><TD>10-16-2018</TD><TD>West</TD><TD>RAJ</TD><TD>75</TD><TD>9834738</TD></TR><TR><TD>10000008</TD><TD>Neel Nitin</TD><TD>10-17-2018</TD><TD>South</TD><TD>GUJ</TD><TD>76</TD><TD>763476</TD></TR><TR><TD>10000009</TD><TD>Suraj</TD><TD>10-18-2018</TD><TD>North</TD><TD>MUM</TD><TD>54</TD><TD>334455</TD></TR><TR><TD>10000010</TD><TD>Jyoti</TD><TD>10-19-2018</TD><TD>East</TD><TD>NCR</TD><TD>45</TD><TD>243545</TD></TR><TR><TD>10000001</TD><TD>Ramesh</TD><TD>10-10-2018</TD><TD>North</TD><TD>NCR</TD><TD>50</TD><TD>454645</TD></TR><TR><TD>10000002</TD><TD>Ram</TD><TD>10-11-2024</TD><TD>East</TD><TD>TN</TD><TD>45</TD><TD>343645</TD></TR><TR><TD>10000003</TD><TD>Shayam</TD><TD>12-19-2024</TD><TD>West</TD><TD>UP East</TD><TD>64</TD><TD>132323</TD></TR><TR><TD>10000004</TD><TD>Kumar Bisvas</TD><TD>10-13-2018</TD><TD>South</TD><TD>AP</TD><TD>35</TD><TD>4545464</TD></TR><TR><TD>10000005</TD><TD>Raju</TD><TD>10-14-2018</TD><TD>North</TD><TD>WB</TD><TD>67</TD><TD>2343432</TD></TR><TR><TD>10000006</TD><TD>Ram Gopal</TD><TD>10-15-2018</TD><TD>East</TD><TD>KTK</TD><TD>45</TD><TD>8735454</TD></TR><TR><TD>10000007</TD><TD>Kamal</TD><TD>10-16-2018</TD><TD>West</TD><TD>RAJ</TD><TD>75</TD><TD>9834738</TD></TR><TR><TD>10000008</TD><TD>Neel Nitin</TD><TD>10-17-2018</TD><TD>South</TD><TD>GUJ</TD><TD>76</TD><TD>763476</TD></TR><TR><TD>10000009</TD><TD>Suraj</TD><TD>10-18-2018</TD><TD>North</TD><TD>MUM</TD><TD>54</TD><TD>334455</TD></TR><TR><TD>10000010</TD><TD>Jyoti</TD><TD>10-19-2018</TD><TD>East</TD><TD>NCR</TD><TD>45</TD><TD><P>243545</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>RESULT</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Customer Code</TD><TD>MTD SALE</TD><TD>QTD SALE</TD><TD>YTD SALE</TD></TR><TR><TD>10000001</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000002</TD><TD>264646</TD><TD>0</TD><TD>264646</TD></TR><TR><TD>10000003</TD><TD>0</TD><TD>687290</TD><TD>687290</TD></TR><TR><TD>10000004</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000005</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000006</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000007</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000008</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000009</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>10000010</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR></TBODY></TABLE> Thu, 19 Dec 2024 00:53:34 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335805#M172165 proavinash 2024-12-19T00:53:34Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335809#M172166 <P>NEED RESULT LIKE CUSTOMER TABLE.</P> Thu, 19 Dec 2024 00:57:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335809#M172166 proavinash 2024-12-19T00:57:22Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335890#M172171 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="859703" data-lia-user-login="proavinash" class="lia-mention lia-mention-user">proavinash</a>&nbsp;<BR /><BR />Thanks for the reply from&nbsp;Kedar_Pande,&nbsp;ThxAlot&nbsp;and&nbsp;lbendlin. You can use the dax to achieve your need. By the way, note&nbsp;that there seems to be a problem with the MTD and QTD you posted for 1002 and 1003, the data in the following screenshot seems to be the correct result.<BR /><BR /></P> <P><img /><BR />Measure:&nbsp;</P> <LI-CODE lang="markup">SaleMTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear &amp;&amp; MONTH ( _date ) = _todayMonth &amp;&amp; _date &lt;= TODAY (), SUM ( Demand[Net Sale] ), 0 ) RETURN _re SaleQTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear &amp;&amp; QUARTER ( _date ) = _todayQuarter &amp;&amp; _date &lt;= TODAY (), SUM ( Demand[Net Sale] ), 0 ) RETURN _re SaleYTD = VAR _todayYear = YEAR ( TODAY () ) VAR _todayQuarter = QUARTER ( TODAY () ) VAR _todayMonth = MONTH ( TODAY () ) VAR _date = SELECTEDVALUE ( Demand[Date] ) VAR _re = IF ( YEAR ( _date ) = _todayYear, SUM ( Demand[Net Sale] ), 0 ) RETURN _re</LI-CODE> <P>&nbsp;</P> <P style="text-align: justify; text-justify: inter-ideograph;"><SPAN>Best Regards,<BR />Yang</SPAN><SPAN><BR /></SPAN><SPAN>Community Support Team</SPAN></P> <P style="text-align: justify; text-justify: inter-ideograph;">&nbsp;</P> <P style="background: white;"><SPAN>If there is any post </SPAN><STRONG><SPAN>helps</SPAN></STRONG><SPAN>, then please consider </SPAN><STRONG><SPAN>Accept it as the solution</SPAN></STRONG><SPAN>&nbsp;to</SPAN><SPAN>&nbsp;help the other members find it more quickly.<BR />If I misunderstand your needs or you still have problems on it, please feel free to let us know. </SPAN><STRONG><SPAN>Thanks a lot!</SPAN></STRONG></P> <P style="background: white;"><U><SPAN><A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommunity-Blog%2FHow-to-Get-Your-Question-Answered-Quickly%2Fba-p%2F38490&amp;data=05%7C01%7Cv-yaningyang%40microsoft.com%7C326a6d727194478c197008dbefc04904%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638367381365912356%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=7IzyI2%2FMOAR%2FYCjWYSKCZL2VPmT4PsxuWBjdUdMWKzo%3D&amp;reserved=0" target="_blank">How to get your questions answered quickly</A></SPAN></U><SPAN>&nbsp;-- </SPAN><U><SPAN><A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommunity-Blog%2FHow-to-provide-sample-data-in-the-Power-BI-Forum%2Fba-p%2F963216&amp;data=05%7C01%7Cv-yaningyang%40microsoft.com%7C326a6d727194478c197008dbefc04904%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638367381365919045%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=VeafzdwAsrpisD8s261%2FdyOwJ1aCtBXS3bPA6NODOHY%3D&amp;reserved=0" target="_blank">&nbsp;How to provide sample data in the Power BI Forum</A></SPAN></U></P> Thu, 26 Dec 2024 08:24:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4335890#M172171 Anonymous 2024-12-26T08:24:44Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4337224#M172211 <P>Quite some shxty rookies, like this one, order other around and take for granted that any others are supposed to help.</P> Thu, 19 Dec 2024 15:16:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4337224#M172211 ThxAlot 2024-12-19T15:16:15Z Re: How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4338795#M172290 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="859703" data-lia-user-login="proavinash" class="lia-mention lia-mention-user">proavinash</a>&nbsp;</P><P>Ensure there is a relationship between the Customer Table[Customer Code] and Sale Dump Table[Customer code].</P><PRE>MTD Sale = <BR />CALCULATE(<BR />SUM('Sale Dump Table'[Sale Net]),<BR />DATESMTD('Sale Dump Table'[date])<BR />)</PRE><P><span class="lia-unicode-emoji" title=":love_letter:">💌</span> <STRONG>If this helped, a Kudos <span class="lia-unicode-emoji" title=":thumbs_up:">👍</span> or Solution mark <span class="lia-unicode-emoji" title=":white_heavy_check_mark:">✅</span> would be great! <span class="lia-unicode-emoji" title=":party_popper:">🎉</span></STRONG><BR /><STRONG>Cheers,</STRONG><BR /><STRONG>Kedar</STRONG><BR /><STRONG><A href="https://www.linkedin.com/in/kedar-pande" target="_blank" rel="noopener">Connect on LinkedIn</A></STRONG></P> Fri, 20 Dec 2024 14:01:35 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-use-SUMIFS-in-power-BI-Customer-wise-MTD-sale-when/m-p/4338795#M172290 Kedar_Pande 2024-12-20T14:01:35Z