web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Bank Reconciliation re...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Bank Reconciliation report - Get last reconciled date based upon reconcile ID

(1) ShareShare
ReportReport
Posted on by CU27030539-1 10

Hi All,

I am working on a Bank Reconciliation report using Electronic Reporting (ER) in Dynamics 365 Finance and Operations. Currently, the report generates the Reconciled Date for each record.

However, the customer has requested to add the previous reconciled date for each record, based on the Reconciliation ID.

I want to retrieve the last reconciled date (the most recent previous record) corresponding to the same Reconciliation ID.

How can I achieve this in Electronic Reporting? Any guidance or sample expressions would be appreciated!

 

Categories:
Dynamics 365 Finance
BRS.png
I have the same question (0)
  • Suggested answer
    Holly Huffman Profile Picture
    Holly Huffman 6,530 Super User 2025 Season 2 on at
    Hi there! Good morning, evening, or afternoon - depending on where you are :) Hope you are well today! 
     
    To retrieve the last reconciled date (the most recent previous record) based on the Reconciliation ID in a Bank Reconciliation report using Electronic Reporting (ER) in Dynamics 365 Finance and Operations, you can follow these steps:
    1. Sort and Group Data
    • Use the ER framework to sort the reconciliation records by Reconciliation ID and Reconciled Date in descending order.
    • Group the data by Reconciliation ID to isolate records for each ID.
    2. Use Aggregation
    • Apply aggregation functions to retrieve the maximum reconciled date for each Reconciliation ID. This will help identify the most recent reconciled date.
    3. Lookup Previous Date
    • Implement a lookup or custom function to find the previous reconciled date for each record. You can use ER expressions to compare the current record's reconciled date with the previous record's reconciled date.
    Sample Expression
    Here’s an example of how you can structure the ER expression:
    <Aggregate>
      <GroupBy>
        <Field>ReconcileID</Field>
      </GroupBy>
      <SortBy>
        <Field>ReconciledDate</Field>
        <Order>Descending</Order>
      </SortBy>
      <Select>
        <Field>ReconcileID</Field>
        <Field>ReconciledDate</Field>
        <Function>Max</Function>
      </Select>
    </Aggregate>
     
    This expression groups the records by Reconciliation ID, sorts them by Reconciled Date in descending order, and selects the maximum reconciled date for each group. You can then use this data to find the previous reconciled date for each record.
    For more detailed guidance, you can refer to the official documentation on Electronic Reporting.
  • CU27030539-1 Profile Picture
    CU27030539-1 10 on at
    Hi Holly,

    I tired this but there is no MAX() in Electronic Reporting, I tried with using calculated fields, if it's possible please give me detailed answer, and thank you so much for your response.

    Thanks!
  • Raj Borad Profile Picture
    Raj Borad 1,428 on at
    Hi,
     
    You can try the orderby and first functions of the ER.
    Try below ER Formula:
    LOOKUP(
        FILTER(BankReconciliationTable, 
            BankReconciliationTable.ReconciliationID = CurrentRecord.ReconciliationID
        ),
        ORDERBY(BankReconciliationTable.ReconciledDate, Descending),
        INDEX(2)
    ).ReconciledDate


    Sort records by Reconciled Date in descending order
    Use the FIRST function to retrieve the previous reconciled date.
     
    Thanks.
  • CU27030539-1 Profile Picture
    CU27030539-1 10 on at
    Hi Raj,

    Thanks for the reply, sure I'll try and let you know the status

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 584 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 499 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 254 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans