topic Re: Incorrect totals with LASTNONBLANK. in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4041400#M160243 <P><FONT>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="280529" data-lia-user-login="RobRayborn" class="lia-mention lia-mention-user">RobRayborn</a></FONT>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>Could you please share some sample data and the expected results based on the sample data? So that I can help you better.&nbsp;</SPAN><A href="https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216" target="_blank" rel="noopener">How to provide sample data in the Power BI Forum - Microsoft Fabric Community</A><SPAN>&nbsp;Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.</SPAN></P> <P>&nbsp;</P> <P><SPAN>Best Regards,<BR />Yulia Xu</SPAN></P> Mon, 15 Jul 2024 02:39:08 GMT Anonymous 2024-07-15T02:39:08Z Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4041216#M160236 <P>I have files I receive monthly that I use to update shipping requirments month after month.&nbsp;<BR />Each file has a list of products as well as the ship month and the quantity that is to ship in those months.&nbsp; I add a 'Date Create' to know when I received the file.<BR />I use the SUMX function to sum all the data, then use the CALCULATE function to get the last non-blank value.&nbsp;<BR />When I add the 'Date Created to the Rows along with the Product, each row total is correct, however the grand total is not.&nbsp;<BR />I'm attaching some sample data in a .pbix file that will show what I am seeing.&nbsp;<BR />I need to show the Grand Total with corect quantitys when it is presented.<BR />Please help me with this function to get the data I need.&nbsp; I've reviewed multiple YouTube videos concerning "Incorrect Totals", and I have not been able to figure this out.&nbsp;&nbsp;<BR />ChatGPT is stumped as well.</P> Sun, 14 Jul 2024 21:00:41 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4041216#M160236 RobRayborn 2024-07-14T21:00:41Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4041400#M160243 <P><FONT>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="280529" data-lia-user-login="RobRayborn" class="lia-mention lia-mention-user">RobRayborn</a></FONT>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>Could you please share some sample data and the expected results based on the sample data? So that I can help you better.&nbsp;</SPAN><A href="https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216" target="_blank" rel="noopener">How to provide sample data in the Power BI Forum - Microsoft Fabric Community</A><SPAN>&nbsp;Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.</SPAN></P> <P>&nbsp;</P> <P><SPAN>Best Regards,<BR />Yulia Xu</SPAN></P> Mon, 15 Jul 2024 02:39:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4041400#M160243 Anonymous 2024-07-15T02:39:08Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042390#M160311 <P class="lia-align-left">Date CreatedProductShip DateQty.</P><TABLE><TBODY><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>1/1/2024</TD><TD>230</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>2/1/2024</TD><TD>880</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>3/1/2024</TD><TD>699</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>4/1/2024</TD><TD>733</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>5/1/2024</TD><TD>254</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>6/1/2024</TD><TD>798</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>7/1/2024</TD><TD>271</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>8/1/2024</TD><TD>437</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>9/1/2024</TD><TD>532</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>10/1/2024</TD><TD>275</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>11/1/2024</TD><TD>324</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>12/1/2024</TD><TD>1080</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>1/1/2025</TD><TD>317</TD></TR><TR><TD>12/10/2023</TD><TD>AAA</TD><TD>2/1/2025</TD><TD>966</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>1/1/2024</TD><TD>1088</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>2/1/2024</TD><TD>547</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>3/1/2024</TD><TD>95</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>4/1/2024</TD><TD>668</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>5/1/2024</TD><TD>464</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>6/1/2024</TD><TD>136</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>7/1/2024</TD><TD>747</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>8/1/2024</TD><TD>517</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>9/1/2024</TD><TD>454</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>10/1/2024</TD><TD>995</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>11/1/2024</TD><TD>761</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>12/1/2024</TD><TD>684</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>1/1/2025</TD><TD>275</TD></TR><TR><TD>12/10/2023</TD><TD>BBB</TD><TD>2/1/2025</TD><TD>394</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>1/1/2024</TD><TD>580</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>2/1/2024</TD><TD>755</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>3/1/2024</TD><TD>681</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>4/1/2024</TD><TD>150</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>5/1/2024</TD><TD>674</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>6/1/2024</TD><TD>589</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>7/1/2024</TD><TD>263</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>8/1/2024</TD><TD>953</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>9/1/2024</TD><TD>1015</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>10/1/2024</TD><TD>1043</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>11/1/2024</TD><TD>131</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>12/1/2024</TD><TD>27</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>1/1/2025</TD><TD>361</TD></TR><TR><TD>12/10/2023</TD><TD>CCC</TD><TD>2/1/2025</TD><TD>1051</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>2/1/2024</TD><TD>99</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>3/1/2024</TD><TD>772</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>4/1/2024</TD><TD>465</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>5/1/2024</TD><TD>133</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>6/1/2024</TD><TD>97</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>7/1/2024</TD><TD>384</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>8/1/2024</TD><TD>982</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>9/1/2024</TD><TD>527</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>10/1/2024</TD><TD>268</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>11/1/2024</TD><TD>812</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>12/1/2024</TD><TD>55</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>1/1/2025</TD><TD>189</TD></TR><TR><TD>1/15/2024</TD><TD>AAA</TD><TD>2/1/2025</TD><TD>601</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>2/1/2024</TD><TD>94</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>3/1/2024</TD><TD>757</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>4/1/2024</TD><TD>40</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>5/1/2024</TD><TD>481</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>6/1/2024</TD><TD>248</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>7/1/2024</TD><TD>790</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>8/1/2024</TD><TD>1033</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>9/1/2024</TD><TD>810</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>10/1/2024</TD><TD>346</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>11/1/2024</TD><TD>142</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>12/1/2024</TD><TD>362</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>1/1/2025</TD><TD>449</TD></TR><TR><TD>1/15/2024</TD><TD>BBB</TD><TD>2/1/2025</TD><TD>92</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>3/1/2025</TD><TD>97</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>2/1/2024</TD><TD>637</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>3/1/2024</TD><TD>891</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>4/1/2024</TD><TD>696</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>5/1/2024</TD><TD>331</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>6/1/2024</TD><TD>741</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>7/1/2024</TD><TD>1032</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>8/1/2024</TD><TD>1015</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>9/1/2024</TD><TD>1072</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>10/1/2024</TD><TD>152</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>11/1/2024</TD><TD>633</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>12/1/2024</TD><TD>549</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>1/1/2025</TD><TD>629</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>2/1/2025</TD><TD>604</TD></TR><TR><TD>1/15/2024</TD><TD>CCC</TD><TD>3/1/2025</TD><TD>312</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>3/1/2024</TD><TD>151</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>4/1/2024</TD><TD>541</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>5/1/2024</TD><TD>414</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>6/1/2024</TD><TD>446</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>7/1/2024</TD><TD>546</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>8/1/2024</TD><TD>885</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>9/1/2024</TD><TD>108</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>10/1/2024</TD><TD>854</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>11/1/2024</TD><TD>1091</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>12/1/2024</TD><TD>177</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>1/1/2025</TD><TD>778</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>2/1/2025</TD><TD>926</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>3/1/2025</TD><TD>289</TD></TR><TR><TD>2/15/2024</TD><TD>AAA</TD><TD>4/1/2025</TD><TD>803</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>3/1/2024</TD><TD>917</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>4/1/2024</TD><TD>418</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>5/1/2024</TD><TD>489</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>6/1/2024</TD><TD>528</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>7/1/2024</TD><TD>925</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>8/1/2024</TD><TD>144</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>9/1/2024</TD><TD>1011</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>10/1/2024</TD><TD>552</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>11/1/2024</TD><TD>663</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>12/1/2024</TD><TD>79</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>1/1/2025</TD><TD>10</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>2/1/2025</TD><TD>863</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>3/1/2025</TD><TD>909</TD></TR><TR><TD>2/15/2024</TD><TD>BBB</TD><TD>4/1/2025</TD><TD>1042</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>3/1/2024</TD><TD>294</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>4/1/2024</TD><TD>765</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>5/1/2024</TD><TD>758</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>6/1/2024</TD><TD>1059</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>7/1/2024</TD><TD>1063</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>8/1/2024</TD><TD>807</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>9/1/2024</TD><TD>195</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>10/1/2024</TD><TD>685</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>11/1/2024</TD><TD>251</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>12/1/2024</TD><TD>129</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>1/1/2025</TD><TD>1024</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>2/1/2025</TD><TD>615</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>3/1/2025</TD><TD>76</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>4/1/2025</TD><TD>773</TD></TR><TR><TD>2/15/2024</TD><TD>CCC</TD><TD>5/1/2025</TD><TD>415</TD></TR></TBODY></TABLE> Mon, 15 Jul 2024 10:51:41 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042390#M160311 RobRayborn 2024-07-15T10:51:41Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042392#M160312 <P>Is there a way for me to send you or upload the sample .pbix file?</P><P>&nbsp;</P> Mon, 15 Jul 2024 10:53:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042392#M160312 RobRayborn 2024-07-15T10:53:08Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042397#M160313 <P><img /></P><P>&nbsp;</P> Mon, 15 Jul 2024 10:56:13 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4042397#M160313 RobRayborn 2024-07-15T10:56:13Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4045616#M160457 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="280529" data-lia-user-login="RobRayborn" class="lia-mention lia-mention-user">RobRayborn</a>&nbsp;</P> <P>&nbsp;</P> <P>Thanks for sharing the sample data, please try the following formula:</P> <LI-CODE lang="markup">Measure = SUMX(VALUES('YourTableName'[Product]), [YourUsingLASTNONBLANKMeasureName])</LI-CODE> <P>&nbsp;</P> <P>If the problem persists then I may need to know how your formula is written so I can test it.</P> <P>&nbsp;</P> <P>Best Regards,<BR />Yulia Xu</P> <P>&nbsp;</P> <P>If this post <EM><STRONG>helps</STRONG></EM>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P> Wed, 17 Jul 2024 07:45:13 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4045616#M160457 Anonymous 2024-07-17T07:45:13Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4057227#M161021 <P>&nbsp;Did not work.</P><P><img /></P><P>&nbsp;</P> Tue, 23 Jul 2024 19:20:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4057227#M161021 RobRayborn 2024-07-23T19:20:49Z Re: Incorrect totals with LASTNONBLANK. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4075697#M161825 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="280529" data-lia-user-login="RobRayborn" class="lia-mention lia-mention-user">RobRayborn</a>&nbsp;</P> <P>&nbsp;</P> <P>Please refer to the following test:</P> <P>&nbsp;</P> <P>Create measures as follow</P> <P>&nbsp;</P> <LI-CODE lang="markup">Measure2 = IF ( ISFILTERED ( 'Table'[Date Created] ), [YourUsingLASTNONBLANKMeasureName], IF ( ISFILTERED ( 'Table'[Product] ), SUMX ( VALUES ( 'Table'[Date Created] ), [YourUsingLASTNONBLANKMeasureName] ), SUMX ( VALUES ( 'Table'[Product] ), [YourUsingLASTNONBLANKMeasureName] ) ) )</LI-CODE><LI-CODE lang="markup">Measure3 = IF( ISFILTERED('Table'[Ship Date]), [Measure2], SUMX(VALUES('Table'[Ship Date]), [Measure2]) )</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <P>Output:</P> <P><img /></P> <P>&nbsp;</P> <P>Best Regards,<BR />Yulia Xu</P> <P>&nbsp;</P> <P>If this post <EM><STRONG>helps</STRONG></EM>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P> Mon, 05 Aug 2024 08:02:29 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Incorrect-totals-with-LASTNONBLANK/m-p/4075697#M161825 Anonymous 2024-08-05T08:02:29Z