Invoice payment and running balance YoY

arve.haugland · January 27, 2022, 10:14pm

Customer Receivables DEMO.pbix (1.2 MB)
Hi all,

I am trying to understand how to compare the running balance of the accounts receivable month by month, year by year etc, but I find myself stuck at the moment.

I have the open value pt (invoices cleared/posted (not paid) and the cumulative total of this, but I am not able to find the culumative total of the open value at a previous date.
Using the current data many of the invoices/documents that were open at a previous date is now cleared (closed/paid) and I know that I have to consider if the document is cleared and if the clearing date is on or after the date.

Attached below is a picture of the most important columns in the data source, the invoice document number, the document date of the receivable was received, the due date, the clearing date showing the date when the document was closed/paid.
2022-01-27 23_15_38-FBL5N ALL EXPORT.XLSX - Excel

However, I can’t seems to find the correct approach, so I would appreciate how this can be done.
I have attached a PBIX file if anyone would like to play around with it.

With this data available I aim to compare year by year (e.g. 2020 and 2021) on a monthly basis, as well as the percentage change from last month. See attached picture for the drafted layout.

arve.haugland · January 28, 2022, 8:31am

By adding a custom column to my fact table I believe I have a working solution for finding the last month value when considering when the document/invoice was created and when it potentially was paid.

LM Receivable Value = 
IF( 
    'Customer Line Items (FBL5N)'[Document Date] <= [Date LM], 
    IF( 
        'Customer Line Items (FBL5N)'[Clearing date] = BLANK(), 
        'Customer Line Items (FBL5N)'[Amount in local currency], 
        IF(
            'Customer Line Items (FBL5N)'[Clearing date] > [Date LM], 
            'Customer Line Items (FBL5N)'[Amount in local currency],
            0
        )
    ),
    0
)

By starting with the custom column it should be easier to create a measure doing the very same thing.

My aim is to compare the last two years in a line chart, month by month.
The only approach I can think of right now that might work is to create one measure for each period, but I trust there is a smarter solution for this.
Anyone have a suggestion on how to move forward from here?

JoeRobert · January 28, 2022, 6:32pm

Not sure if I understand your ask here, at least in reference between the clearing and posted, but i took a stab at your pbix file. If you are looking for MOM calculation, all you need is a simply time intelligence function in your measure to offset the datestamp. I sum aggregated the ‘amount in local currency’ column and overlaid that over another measure that shifts the dates back by one year. You already had the data table set up so this was easy. This does not look as smooth as your picture before, you can apply a 60day rolling average (or something like that) to give it more appeal. Also if you are wanting to exclude the $ invoices that have not cleared, you can include a filter argument to filter out those blank rows.

Customer Receivables DEMO.pbix (1.2 MB)

arve.haugland · January 29, 2022, 9:53am

Thank you Joe,

My initial question and explanation was not very well worded so I can understand you had a challenge interpreting what I was really asking for.
Also, I understood while writing the first post, that the naming convention would be confusing. I have not made it more intuitive because the end users work with these names all day in SAP so for them it all makes sense, but for the purpose of the demo I should have changed them.

Ok, back to the report.
I really appreciate your feedback, but the is one extra layer of complexity which is what I struggle with.

With the data source that I have I can, as you know, easily tell which invoices are open and which have been paid (cleared).
However, if I would like to know which invoices were open and paid one month or one year back, I need to understand the environment around the context at that time, meaning I need to consider the document date and the clearing date against the calendar date.

I’ll try to be even clearer on what I believe I have to do (at least I can do it this way in excel).

It is only the “Document date” and “Clearing date” that it is important right now.
Document date = When is the invoice created.
Clearing date = When is the invoice paid.

To understand if I should include the invoice amount for a certain calendar date I need to check the following;

Document date must be before or on the calendar date.
The Clearing date must be empty or before the calendar date.

I have attached I picture of a sample from Excel showing the logic. I have used two different blue saturations to easier differentiate between the lines.
I guess I can make the same in PBI with custom columns, but I’m sure it is possible to make a smarter solution through measures…

JoeRobert · January 30, 2022, 12:54am

Question: In your demo pbix file, your date table was related to the ‘customers Line Item’[Document Date]. Having said that, what do you mean “document date must be before or on the calendar date”? In my mind, the clearing date will never be before the calendar date.

deltaselect · January 30, 2022, 1:22pm

Hi @arve.haugland ,

Please receive hereby a solution for an Accounts Receivable reporting over the months, whereby it is possible to compare with Last Year and Last month, based upon the EDNA courses Financial Reporting , Aged Trial Balance.

PBIX attached:
Customer Receivables DEMO DS v2.pbix (1.2 MB)

Work out described:

Adjust an Aging Grouping table, adding a Current bucket (-90-0) and an AR-group (see below)

2. Adjust the data model (see below), no connection allowed between the Dates table and the AR Subledger, as all non-cleared invoices have to be included for aging analysis, and with a date connection the invoices would be restricted (filtered) by the selected period.

3. Create measures for Days Due and Receivables per Group, for the Current Period, Previous Month and Last Year, to be able to compare accordingly, which generates the Aging Buckets.
(see also the explanatory text in the measures below)

Current Days Due = 
Var ReportingDate = MAX(Dates[Date])  //period max date (Dec2021 = 31.12.2021) (NON !! connected date table)
Var NetDueDate = MIN(ARFBL5N[Net due date] )
Var DocumentDate = MIN( ARFBL5N[Document Date])
Var ClearingDate = MIN(ARFBL5N[Clearing date])
Var DaysDue3 = IF( or( DocumentDate > ReportingDate, ClearingDate <> BLANK()),  blank(), 
            //blank when invoice date > reporting period end date, or clearing date on/before reporting date: blanc, no need for Days due calculation, as those documents are settled
                    DATEDIFF( NetDueDate, ReportingDate, DAY))  
                    // includes all invoices without an clearing date up untill the reporting period , if not due, then a negative Days due, which goes into Current group 
return DaysDue3

Current Receivables Per Group = 
VAR FilteredARLedger   =   FILTER ( ARFBL5N,
                                and( ARFBL5N[Document Date] <= max(Dates[Date]), --filter all document lines before max date from reporting period (e.g. 31.10.2021 for "okt 2021")
                                    ISBLANK( ARFBL5N[Clearing date])))  -- which does NOT have a clearing date  

Var AgingGroup =  CALCULATE( SUM( ARFBL5N[Amount in local currency] ),
                      FILTER( FilteredARLedger, --to speed up the calculation, as the data is filtered
                        COUNTROWS(
                            FILTER( 'Aged2 Debtor Groups',
                                [Current Days Due] >= 'Aged2 Debtor Groups'[Min2] &&
                                [Current Days Due] <= 'Aged2 Debtor Groups'[Max2] ) ) > 0 ) )
 return AgingGroup

The comparison measures Last Month and Last Year “Days Due” and “Receivables Per Group” (4 in total) are set up the same, with only a variation in “MAX(Dates[Date])” and using the belonging Days Due measure: (the Max Date is based upon selected period, Last Month and Last Year are derived from this date, see also Note 2 below)

in Last Month Days Due = Var ReportingDate = EOMONTH(MAX(Dates[Date]),-1)
in Last Month Receivables Per Group = VAR FilteredARLedger = … EOMONTH(MAX(Dates[Date]),-1)
in Last Month Receivables Per Group use [Last Month Days Due]
in Last Year Days Due = Var ReportingDate = EOMONTH(MAX(Dates[Date]),-12)
in Last Year Receivables Per Group = VAR FilteredARLedger = … EOMONTH(MAX(Dates[Date]),-12)
in Last Year Receivables Per Group use [Last Year Days Due]

Note 1: all measures can be found in measure group “DS Measures”
Note 2: measure branching using DateAdd to createLast Month and Last Year with the initial measure, does not work as the Date Table is not connected with the AR Ledger fact table.
Note 3: to let the grouping calculation work, measure branching is needed in “Current Receivables Per Group”, using " Current Days Due" as a separate measure. Integrating " Current Days Due" in “Current Receivables Per Group” results in all receivables being “Current”, does not work.

4. Reports can be made with comparison per month, see below basic examples:
(As a lot of unsettled documents with large negative amounts exist in the data, negative AR-balances appears.)

image1307×679 56.4 KB

image1117×745 46.7 KB

An audit report is also added to control individual periods:

image1013×633 49.6 KB

The EDNA courses Financial Reporting , Aged Trial Balance, for aging reporting:
–Advanced dynamic grouping modelling and formula techniques

– Visualization options

I hope this solution helps you further, please let me know any comments,

kind regards, Jan van der Wind

arve.haugland · January 30, 2022, 9:59pm

Hi @deltaselect,

Thank you very much for your suggestion for solution.
I’ll look more closely into it tomorrow, but so far it seems like a good solution to my challenge.
I’ll also make sure to give priority to the two courses you mention.

The only thing I see I have to add is to make sure a document with a cleared date, is considered as not cleared if the cleared date is after the reporting date.
Document 5130006088 is a good example; The document has Nov 2 2021 as the clearing date, but was not included in the receivables amount for October (date 31 Oct 2021).

I added this logic into the measures you created.

Measure Current Days Due (added ClearingDate <= ReportingDate)
Var DaysDue3 = IF( or( DocumentDate > ReportingDate, and(ClearingDate = BLANK(), ClearingDate <= ReportingDate)), blank()

Measure Current Receivables Per Group (added ARFBL5N[Clearing date] > ReportingDate)
VAR FilteredARLedger = FILTER( ARFBL5N, and( ARFBL5N[Document Date] <= ReportingDate, or( ISBLANK( ARFBL5N[Clearing date]), ARFBL5N[Clearing date] > ReportingDate )))

I find it intriguing that I should not connect the fact table to the date table, but the more I learn, the better I understand how much I still have to learn

Kind regards
Arve

deltaselect · January 30, 2022, 11:29pm

Hi @arve.haugland ,

You are right, the total Aging Receivables must also include the clearing date <= the reporting date.
However the aging buckets calculation goes wrong with this change.
Not sure if the combination of Non due AR and Aging in my set up works, which differs from the explanation of EDNA, to be continued.

Kind regards,
Jan van der Wind

arve.haugland · January 31, 2022, 11:28am

Hi @deltaselect,

Thank you again.
I see that by inserting MAX ( MAX(ARFBL5N[Clearing date]) ) the total becomes too high, and from the looks of it, it includes the value where output the measure [Current Receivables Per Group] is blank.

By removing the MAX for clearing date, the values seems to be correct.

Current Receivables Per Group = 
VAR FilteredARLedger   =   FILTER ( ARFBL5N,
                                and( ARFBL5N[Document Date] <= max(Dates[Date]), --filter all document lines before max date from reporting period (e.g. 31.10.2021 for "okt 2021")
                                  //  or(ISBLANK( ARFBL5N[Clearing date]), MAX(ARFBL5N[Clearing date])>max(Dates[Date]))))  -- which does NOT have a clearing date  or the clearing date > reporting date
                                    or(ISBLANK( ARFBL5N[Clearing date]), ARFBL5N[Clearing date]>max(Dates[Date]))))

Var AgingGroup =  CALCULATE( SUM( ARFBL5N[Amount in local currency] ),
                        FILTER( FilteredARLedger, --to speed up the calculation, as the data is filtered
                        COUNTROWS(
                            FILTER( 'Aged2 Debtor Groups',
                                [Current Days Due] >= 'Aged2 Debtor Groups'[Min2] &&
                                [Current Days Due] <= 'Aged2 Debtor Groups'[Max2] ) ) > 0 ) ) 
 return AgingGroup
 --if(max(Dates[Date]) > TODAY(), BLANK(),  AgingGroup) not to present future periods

Kind Regards
Arve

deltaselect · January 31, 2022, 2:28pm

Hi @arve.haugland

regrettable no solution. A small sample of 500 invoices over 3 years would speed up the detail checking, my idea to solve is to concentrate on current days due, all not allowed /needed should be blank, check in the Audit page if for only the right invoices the days due are calculated, and use this as a filter in Receivables per Group.
Do you have a small meaningful example ?
Kind regards, Jan van der Wind

arve.haugland · February 2, 2022, 9:10am

Hi @deltaselect ,

Finally cleared some deadline work and can focus a bit more on this over the next days.

I have reduced the sample data to approximately 500 lines and things seems to be working fine now. The next step will be to introduce more data to divide by the figures segments as well, but I believe that should be fairly simple.

I’ll tag your initial post as the solution, and again, thank you for your efforts in pushing me in the right direction. It is much appreciated!

I have attached the latest PBIX file for future reference.
Customer Receivables DEMO DS v2_1corrected.pbix (424.6 KB)

Kind Regards
Arve Haugland

deltaselect · February 2, 2022, 11:39am

Hi @arve.haugland (v2)

Thank you for the feedback, and the help with the formula.

After some further testing, including your sample, the total Accounts Receivable AND the split into segments / AR Aging groups, works fine, for the Current period, Last month and for Last Year.

PBIX 3x attached:
Customer Receivables DEMO DS v2_2 ArveHaugland.pbix (407.5 KB)

Cust Receiv DEMO DS audit Oct sample v2.pbix (359.4 KB)

Customer Receivables DEMO DS v3.pbix (1.2 MB)

Easily to check in the page “Aging over periods” in each of the PBIX files.

Just to be able to verify the calculated amounts per AR aging segment /bucket, as presented in the report, calculated columns are added for Receivables and Aging for Oct, Nov and Dec 2021 in the table ‘ARFBL5N’ in "Cust Receiv DEMO DS audit Oct sample v2.pbix "

All measures are based upon the same filter selection, see four of the six measures used below,

Current Days Due = 
Var ReportingDate = MAX(Dates[Date]) --period max date (Dec2021 = 31.12.2021) (NON !! connected date table)
Var NetDueDate = MIN('ARFBL5N'[Net due date] )
Var DocumentDate = MIN( 'ARFBL5N'[Document Date])
Var ClearingDate = Max('ARFBL5N'[Clearing date])
Var DaysDue3 =  if(and( DocumentDate <= ReportingDate, --filter upon all invoices before reporting date,
                     or(ISBLANK( ClearingDate), ClearingDate > ReportingDate)), --either without settlement date or settled after reporting date 
                           DATEDIFF( NetDueDate, Reportingdate, DAY), blank())
return DaysDue3

Current Receivables Per Group = 
VAR FilteredARLedger   =   FILTER ( 'ARFBL5N',
                                and( 'ARFBL5N'[Document Date] <= max(Dates[Date]), --filter upon all invoices before reporting date
                                    or(ISBLANK( 'ARFBL5N'[Clearing date]), 'ARFBL5N'[Clearing date]>max(Dates[Date]))))  -- without clearing date  or clearing date > reporting date
Var AgingGroup =  CALCULATE( SUM( 'ARFBL5N'[Amount in local currency] ),
                      FILTER( FilteredARLedger,  --to speed up the calculation, as the data is filtered
                        COUNTROWS(
                            FILTER( 'Aged2 Debtor Groups',
                                [Current Days Due] >= 'Aged2 Debtor Groups'[Min2] &&
                                [Current Days Due] <= 'Aged2 Debtor Groups'[Max2] ) ) > 0 ) )
 return AgingGroup
 --if(max(Dates[Date]) > TODAY(), BLANK(),  AgingGroup) not to present future periods

Last Month Days Due = 
Var ReportingDate = eomonth(max(Dates[Date]),-1)  -- last month max date (Dec2021 = 30.11.2021) (NON !! connected date table)
Var NetDueDate = MIN('ARFBL5N'[Net due date] )
Var DocumentDate = MIN( 'ARFBL5N'[Document Date])
Var ClearingDate = Max('ARFBL5N'[Clearing date])
Var DaysDue3 =  if(and( DocumentDate <= ReportingDate, --filter upon all invoices before reporting date,
                     or(ISBLANK( ClearingDate), ClearingDate > ReportingDate)), --either without settlement date or settled after reporting date 
                           DATEDIFF( NetDueDate, Reportingdate, DAY), blank())
return DaysDue3

Last Month Receivables Per Group = 
VAR FilteredARLedger   =   FILTER ( 'ARFBL5N',
                                and( 'ARFBL5N'[Document Date] <= EOMONTH(MAX(Dates[Date]),-1), --filter all document lines before Last Month reporting period 
                                    or(ISBLANK( 'ARFBL5N'[Clearing date]), 'ARFBL5N'[Clearing date] > EOMONTH(MAX(Dates[Date]),-1))))  -- without clearing date  or clearing date > Last month reporting date

Var AgingGroup =  CALCULATE( SUM( 'ARFBL5N'[Amount in local currency] ),
                      FILTER( FilteredARLedger, --to speed up the calculation, as the data is filtered
                        COUNTROWS(
                            FILTER( 'Aged2 Debtor Groups',
                                [Last Month Days Due] >= 'Aged2 Debtor Groups'[Min2] &&
                                [Last Month Days Due] <= 'Aged2 Debtor Groups'[Max2] ) ) > 0 ) )
 return AgingGroup

Kind regards, Jan van der Wind