Dynamic AR report

[ylemire]

Hi everyone, I'm trying to to create a report that allows users to age their accounts receivables based on a user-selected date. The report need to show the outstanding dollar value bucketed in different date ranges based on this date. Plus I want to be able to pick any date in the past and find out the value of invoices that were overdue at that point in time.

The transaction process goes like this :

1. Invoice is created at a point in time
2. Payment is received from the customer and a payment transaction is created
3. Payment is “allocated” to the invoice

The allocation table is where the invoice and payment are allocated against each other, meaning that the system has linked the payment  with the invoice and the outstanding debt is therefore zero and the payment is fully cleared down or still open.

I don't know why but for some reasons i'm not able to make this thing work.

Here a link to my Power Bi small scale file : https://1drv.ms/u/s!Ah5EGu-Zf2CbhBxnszYxzRCpiUx0

Could anyone help me with this ?

Thanks

[Anonymous]

Hi @ylemire ,

You can use calendar date to create chart and write a measure to looping 'allocated' and 'customer' tables to find out correspond records and do aggregate on these records.

Not cleared=
VAR currDate =
    MAX ( 'Calendar'[Date] )
VAR invoiceEntry =
    CALCULATETABLE (
        VALUES ( 'Allocation table'[Cust_Ledger Entry No] ),
        FILTER (
            ALLSELECTED ( 'Allocation table' ),
            [Posting Date] <= currDate
                && [Document Type] = "Invoice"
        )
    )
VAR auxiliaryEntry =
    CALCULATETABLE (
        VALUES ( 'Customer Auxiliary'[Entry No] ),
        FILTER ( ALLSELECTED ( 'Customer Auxiliary' ), [Closed at date] > currDate )
    )
RETURN
    CALCULATE (
        SUM ( 'Allocation table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Allocation table' ),
            [Cust_Ledger Entry No] IN INTERSECT ( invoiceEntry, auxiliaryEntry )
        )
    )

Regards,

Xiaoxin Sheng

[ylemire]

Thanks Xiaoxin for your input, correct me if i'm wrong but as I understand your measure , only the invoice will be considered into calculation, but I also need the payment to be considered. In my example, the invoice (2,500$)  is fully cleared by the payment (4,800$ and a creditor amount of 2,300 $is still open on the payment line.

Thanks

[Anonymous]

Hi @ylemire ,

Yes, my formula will filter on two table records and do 'INTERSECT' to extract records who existed in two tables.

>> In my example, the invoice (2,500$)  is fully cleared by the payment (4,800$ and a creditor amount of 2,300 $is still open on the payment line.

Do you means you also want to summary not paid amount? Can you please provide some expected result to help us clear your requirement?

Regards,

Xiaoxin Sheng