Unfortunately, this cannot be done simply with a formula. I made a sample for you to refer to.
Hope it will help.
pageextension 50112 ZYCustomerListExt extends "Customer List"
{
layout
{
addafter("Balance (LCY)")
{
field(Day_30; Day_30)
{
Caption = '30 Day';
ApplicationArea = All;
}
field(Day_60; Day_60)
{
Caption = '60 Day';
ApplicationArea = All;
}
field(Day_90; Day_90)
{
Caption = '90 Day';
ApplicationArea = All;
}
field(BeforeDay_90; BeforeDay_90)
{
Caption = 'Before 90 Day';
ApplicationArea = All;
}
}
}
trigger OnAfterGetRecord()
begin
Day_30 := CalCustomerBalance('<-30D>', Today);
Day_60 := CalCustomerBalance('<-60D>', Today);
Day_90 := CalCustomerBalance('<-90D>', Today);
BeforeDay_90 := CalCustomerBalance('<-99Y>', CalcDate('<-90D>', Today));
end;
var
Day_30: decimal;
Day_60: decimal;
Day_90: decimal;
BeforeDay_90: decimal;
local procedure CalCustomerBalance(Expr: Text[30]; ToDate: Date): Decimal
var
CustLedgerEntries: Record "Cust. Ledger Entry";
TargetDate: Date;
TotalAmount: Decimal;
begin
CustLedgerEntries.Reset();
Clear(TargetDate);
TotalAmount := 0;
TargetDate := CalcDate(Expr, Today);
CustLedgerEntries.SetRange("Customer No.", Rec."No.");
CustLedgerEntries.SetRange("Posting Date", TargetDate, ToDate);
CustLedgerEntries.SetRange(Open, true);
CustLedgerEntries.SetAutoCalcFields(Amount);
if CustLedgerEntries.FindSet() then
repeat
TotalAmount := CustLedgerEntries.Amount TotalAmount;
until CustLedgerEntries.Next() = 0;
exit(TotalAmount);
end;
}
