DAX formula for accruals

[macfuller]

I need a correct formula for calculating accruals and I hope a finance type has something ready-made as this ties me in knots. (I think everyone hates accrual accounting.)


We calculate accruals in a somewhat normal accounting way. If I order a $100 widget from Zimmer, we put it on a department's balance sheet as an accrual when we receive it. It then stays as an accrual until we receive a (correct) invoice, at which time we change it to an actual expense. So, if I order the widget on Feb 20, it is received on Feb 26, and we receive the invoice on April 03, the department's balance sheet will show for that 1 widget:


February: +$100 (accrual)
March: -$100 for February, +$100 for March
April: -$100 accrual for March, +$100 expense for April
May: $0 (was paid in April)


The tables are arranged such that Orders is 1:many to both Receipts and Vouchers


Order Calendar (Date Table using Order Date) 1:many to Orders

Orders 1:many to Receipts
Orders 1:many to Vouchers

AP Calendar (Date Table) 1:many to Vouchers


For simplicity I created columns in the Receipts table so that I have both the Order Amount (e.g. $100) and the Invoice Received Date from the Voucher file. In the above example then the Order #123 would show values in the linked Receipts table Receipts[PO Amount] = $100, Receipts[Recv Date] = 2/26/19, and the Receipts[Vchr Recv Date] = 4/03/19.


I need a formula that will return that $100 for each time period that the widget was received but not invoiced. A monthly report would look like


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Feb 2019[/TD]
[TD]Mar 2019[/TD]
[TD]Apr 2019[/TD]
[TD]May 2019[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]ZIMMER[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And a daily report would have that $100 for each day from Feb 26 through April 03.


So here is where I get confused in how to construct a proper date measure. Since I want to count the Receipts[PO Amount] for each time period I used the AP Calendar table as a disconnected date table.

Accrual Amount:=
    CALCULATE( SUM( Receipts[PO Amount] ),
       FILTER(Receipts,
        Receipts[Recv Date] >= MIN( 'AP Calendar'[Date])
        && Receipts[Vchr Recv Date] >= MAX('AP Calendar'[Date])
        )
    )

I *think* this is correct - but counterintuitively the clause -- Receipts[Vchr Recv Date] <= MAX('AP Calendar'[Date]) -- returns a higher number than using >=, even though the logic is saying only sum the amount if the receipt and invoice arrived in the same time period which is not what I want.


For my pivot table duplicating the $100 report format above I am also unsure what I am reporting if I use the Order Calendar date field in the Columns section and filter on 2019. Since the Order Calendar is related to the Orders, if I filter from February to June I am grouping the POs that were Ordered in each of those months. But, do I need an additional timeline so that I am relating the AP Calendar (on which the measure is based) to some of the data? Otherwise, how does the pivot table "know" based on the order date whether the order accrued during that month as well? I get numbers for the above, but are they the right numbers?

 

[macfuller]

Altering the signs seems to be giving me a larger but more credible number. But, this raises an accounting issue that I don't know if anyone else has faced. This formula works on a daily basis but run on a monthly basis it counts any amount that accrues during the month even if the item was both received and invoiced within that month. So if something was invoiced 2 days after received in the month the full amount of the purchase shows as accrued for the whole month.

I don't know if other organizations calculate partial accruals - e.g. a $300 purchase has 2 days between receipt and invoice in a 30-day month, so its accrual value is ($300 / 30) * 2 = $20 for that month. Heaven help me trying to write that measure!

Accrual Amount:=CALCULATE (    SUM ( Receipts[Merchandise Recept Amt] ),
    FILTER (
        Receipts,
        Receipts[Recv Date]
            <= MAX ( 'AP Calendar'[Date] )
            && Receipts[Vchr Recv Date]
                >= MIN ( 'AP Calendar'[Date] )
    )
)

 

[gazpage]

All the below IMHO:

Accruals are a balance sheet item and are normally a semi additive measure. In this case you are building them up though so a little different. Even so, you want to calculate the accruals on a particular day, I’ve never done anything but the last day of the period.

Simple answer is to replace the MIN in your last formula with a MAX, though personally I would use a variable for readability.

 

[macfuller]

Do you have your last day of the month formula that you could adapt for me?

Yes, the department itself only needs to know what is outstanding on the last day for their budget vs. actual. But we are curious to see by vendor what we accrue over the course of the month. I've got a stacked area chart with the days on the x-axis and total accruals. Running it by type of product (e.g. stent, catheter) lets us show how the vendors compare in receiving vs. invoicing. We can run a simple date comparison, but putting the dollars in gives us an idea of the value at risk so we can decide which vendors to target.

We're trying to expand our payment terms to a longer net.. if vendor x takes an average of 30 days to pay with $10 million in average accruals, and vendor y has an average of 20 days but only $5 million outstanding, it's probably worth it to negotiate with vendor x first to lengthen the terms. Or, if our delivery schedule suddenly changes so that half of vendor x's deliveries now arrive the 2nd week of the month instead of the last week we'll have $5 million in accruals appearing on the balance sheet even though we didn't change our spend.

 

[gazpage]

What do you get if you replace the MIN in your last measure with MAX, and then plot on a daily timeline? How is it different from what you want to see?