Skip to main content
Solved

End balance of an inventory in warehouses based on date

  • February 4, 2026
  • 6 replies
  • 48 views
K
Freshman II

I’m trying to build a GI report based on INItemSiteHistDay, the end balance of in inventory after a transaction is considered as on hand quantity in the location. A date filter has been added to know a ending balance on the specified date, I have added a condition where INItemSiteHistDay.SDate is less than or equal to a specified date in the filter. The problem is that the report is showing all transactions before the date as shown below.

Is there a way to show only the last transaction as on or before the date specified in the filter?

Best answer by karthikvt78

Hi ​@karthikvt78 

INItemSiteHistDay stores daily history records, so filtering by date will return all records prior to that date. To show only the ending balance, you need to group by Inventory, Warehouse, Location,.. and select the MAX(SDate) on or before the specified date. To do this, you need to perform Customization to create a new DAC by creating a SQL View.

 

Best Regards,

NNT

 

The problem with group is, the ending balance in all rows get added and it shows a cumulative value. i have grouped the inventory, site and location.

6 replies

P
Jr Varsity III
Forum|alt.badge.img
  • premalathan35
  • Jr Varsity III
  • February 4, 2026

​@karthikvt78  It looks like a null condition was added to the SDate field in the Conditions tab.

Premalatha N
    nhatnghetinh
    Captain II
    Forum|alt.badge.img+13
    • nhatnghetinh
    • Captain II
    • February 5, 2026

    Hi ​@karthikvt78 

    INItemSiteHistDay stores daily history records, so filtering by date will return all records prior to that date. To show only the ending balance, you need to group by Inventory, Warehouse, Location,.. and select the MAX(SDate) on or before the specified date. To do this, you need to perform Customization to create a new DAC by creating a SQL View.

     

    Best Regards,

    NNT

     

    I have devoted a great deal of my time and passion to Acumatica ! 🌐https://www.linkedin.com/in/phambaotruong
      K
      Freshman II
      • karthikvt78
      • Author
      • Freshman II
      • February 6, 2026

      Hi ​@karthikvt78 

      INItemSiteHistDay stores daily history records, so filtering by date will return all records prior to that date. To show only the ending balance, you need to group by Inventory, Warehouse, Location,.. and select the MAX(SDate) on or before the specified date. To do this, you need to perform Customization to create a new DAC by creating a SQL View.

       

      Best Regards,

      NNT

       

      so, the conditions may not be possible through Generic Inquiry?

      K
      Freshman II
      • karthikvt78
      • Author
      • Freshman II
      • Answer
      • February 6, 2026

      Hi ​@karthikvt78 

      INItemSiteHistDay stores daily history records, so filtering by date will return all records prior to that date. To show only the ending balance, you need to group by Inventory, Warehouse, Location,.. and select the MAX(SDate) on or before the specified date. To do this, you need to perform Customization to create a new DAC by creating a SQL View.

       

      Best Regards,

      NNT

       

      The problem with group is, the ending balance in all rows get added and it shows a cumulative value. i have grouped the inventory, site and location.

        K
        Freshman II
        • karthikvt78
        • Author
        • Freshman II
        • February 6, 2026

        ​@karthikvt78  It looks like a null condition was added to the SDate field in the Conditions tab.

        null condition was not added

        nhatnghetinh
        Captain II
        Forum|alt.badge.img+13
        • nhatnghetinh
        • Captain II
        • February 6, 2026

        Hi ​@karthikvt78 

        If you only want to display a single line of data, Generic Inquity currently cannot do that. So, as I said, to do this, you need to perform customization to create a new DAC by creating an SQL View. Because SQL View is powerful enough to create execution conditions.

         

        Best Regards,

        NNT

        I have devoted a great deal of my time and passion to Acumatica ! 🌐https://www.linkedin.com/in/phambaotruong
          Terms & ConditionsCookie settingsAccessibility statement
          About Acumatica ERP system
          Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
          © 2008 — 2024  Acumatica, Inc. All rights reserved