Accrual Calculation

[macfuller]

I'm hoping someone has done this already and has some quick formulas to offer - this could be a tough one otherwise.

I would like to show accruals for various time periods. Accrual accounting is complex so my request here is a bit simplified, but it's getting the monthly report tied to a histogram table that's confusing me.

I would like to have a table of accruals such that I can show in what month the accrual hit based on when we received the invoice. Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Months After[/TD]
[TD]May 2018[/TD]
[TD]June 2018[/TD]
[TD]July 2018[/TD]
[TD]Aug 2018[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$x[/TD]
[TD]$x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Looking at this from August as the current month, invoices received in May have been around for 4 months and thus might have accruals in each preceding month 0-3. Invoices from July have only been around 2 months and so could only have accruals in months 0 and 1.

What is an accrual? For our purposes we will define it as a item that was received but not paid for as of the specified time period (usually because we have not received an invoice and cannot pay without one). So the June/2 cell in the table above means that we received the item in June but had not paid for it as of August (June being month 0).
Internally we're debating whether we want to show only accruals that are still there or show anything that was accrued earlier but has now been paid. So for the May column we would only show row 3 since those are still open as of August... but for now we'd like to show anything was was accruing at any given time. If an item worth $200 was received in May but not invoiced until July the $200 should show up as part of the monthly totals for rows 0 (May) and 1 (June).

So the key fields are the Invoice/Voucher $ amount, the date the item was received (accrual start date), and the date it was paid. I use the calculated column as below to tell me how many months the item was in accrual status.

Accrual Duration=IF (
    ISBLANK ( Vouchers[Accrual Start Date] ),
    BLANK (),
        IF (Vouchers[Accrual Start Date] > Vouchers[Payment Date],
        BLANK(),
            IF(ISBLANK ( Vouchers[Payment Date] ),
            DATEDIFF ( Vouchers[Accrual Start Date], TODAY (), MONTH ),
            DATEDIFF ( Vouchers[Accrual Start Date], Vouchers[Payment Date], MONTH )
        )
        )
)

Because DATEDIFF blows up if the 1st date is later than the 2nd I have the test statement. (How could we pay before we receive the item? It's rare but for things like emergency repairs we can pay in advance, and even a single row kicks out the error.)

My first difficulty is in defining the accruals. This measure gives everything that is currently accruing (I think).

Accruals:=CALCULATE (
    SUM(Vouchers[Voucher Amount]),
    FILTER(Vouchers,
    ISBLANK ( Vouchers[Payment Date] )
        && NOT ( ISBLANK ( Vouchers[Accrual Start Date] ) )
)
)

But I want to know items that were in accrual status in prior months even if they're paid currently, so I want to include vouchers that have a payment date where the payment date - the accrual date < the number of months in the selected time period as above. 'AP Calendar' is my related date table.

Accruals :=
CALCULATE (
     SUM(Vouchers[Voucher Amount]),
    FILTER (
        Vouchers,
        NOT ( ISBLANK ( Vouchers[Accrual Start Date] ) )
            && IF (
                ISBLANK ( Vouchers[Payment Date] ),
                TRUE,
                IF (
                    Vouchers[Accrual Start Date] > Vouchers[Payment Date],
                    FALSE,
                    IF (
                        Vouchers[Accrual Start Date] > MAX ( 'AP Calendar'[Date] ),
                        FALSE,
                        DATEDIFF ( Vouchers[Accrual Start Date], MAX ( 'AP Calendar'[Date] ), MONTH )
                            <= Vouchers[Accrual Duration]
                    )
                )
            )
    )
)

I put the Vouchers[Accrual Start Date] > Vouchers[Payment Date] inside the IF statement thinking that if I had it as a separate NOT() then the DATEDIFF problem would still appear. I'll ignore any items that were received after the payment date (Accrual Start Date > Payment Date). Similarly I'll ignore any accruals that start after the selected calendar date.

If the Accrual measure is correct I can then get to my final table with two measures.

Monthly Accrual:=CALCULATE (    [Accruals],
    FILTER ( Vouchers, Vouchers[Accrual Duration] <= MAX ( histogram[Bins] ) )
)

and the final measure

Net Accrual:=CALCULATE (    [Monthly Accrual],
    FILTER ( Vouchers, Vouchers[Accrual Duration] >= MAX ( Histogram[Bins] ) )
)

These return values, but as to whether they're returning the *correct* values is something else.

 

[gazpage]

I’ve literally just done a report on trade debtors and trade creditors based on a list of invoices.

My solution was like.

[Debtors]:=

VAR EndDate = MAX ( Calendar[Date] )

RETURN

CALCULATE (
[Measure],
Filter (
Invoices,
Invoices[Invoice Date] <= EndDate
&& ( Invoices[Payment Date] > EndDate || ISBLANK ( Invoices[Payment Date] ) )
)
)

In my case a blank in the payment date means the invoice is outstanding.