Forum Discussion
Dynamic AR report
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
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
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
