Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[David Stonesifer]

I’m having trouble explaining why some sales numbers between a SmartList report and the Customer Yearly Summary Inquiry aren’t matching.
On one hand, the YTD value on both is spot on, however the Last YR Sales is not even close.  See attachment for reference.  It’s the same issue on all customers, not just the highlighted.

Any help is greatly appreciated.  Thank you.

——————————
David Stonesifer
Senior Manager Business Intelligence
BIG DAISHOWA Inc.
Hoffman Estates IL
——————————

[Thaddeus Suter]

The difference is because of poor year end closing procedures in the Receivables Management module.

The window, Customer Yearly Summary Inquiry displays calculated value(s) from RM00104 table which holds data for each period of each year. The window is date sensitive in that you pick the year and the period then press calculate to get the results.

The Smartlist you have selected is not date sensitive and shows the data from RM00103 and it is current as of right now. You don’t pick a period or year. The Last Year data  does not match the sum of the periods in the other window because of a bad year end close. They closed the year too late or not at all hence the values are too high.

You can likely see the same results in Payables Management under Vendor Yearly Summary Inquiry because since they did a bad close on RM they probably did a bad close on PM as well.

Until you resolve with SQL scripts I would not rely on the Smartlist values but the dex Inquiry window data probably reflects reality as it is calculated.

——————————
Thaddeus Suter
Retus, Inc
HELOTES TX
——————————
——————————————-

[Mark Wiley]

Hi David,

Running a reconcile will not correct the issue. The amount is a column stored in the RM00103 table. The Sales Close process zeros out the YTD bucket, after moving the number to LYR. It is not date sensitive – it just moves YTD to LYR at the time the close button is pushed. So if it’s actually January 10 when the button is pushed (even if the GP date is set to 12/31) any transactions from Jan 1 to Jan 10 will be included in the YTD bucket and moved to LYR.

You either need a SQL script to recalculate (and update) the amounts stored in RM00103, or a SQL view to calculate the amount based on the transaction history. There are also ISV options. If you look at the SQL scripts on Victoria Yudin’s web site, that might help. Generally, we do not recommend that column because it often not accurate.
Link: https://victoriayudin.com/gp-reports/receivables-sql-views/

Hope that helps,

——————————
Mark Wiley
Dynamics Credentialed Professional
Senior Solutions Architect
GraVoc
Ohio Office
——————————
——————————————-