Hi all,
I am creating a open amount history report. The user wants to know the open amount at the specific date. For a selected date(selected from slicer), I want to find out the sum of the payment amount which the selected date are between invoice date and check date. Currently I linked the date from date table to invoice date in AP table, i don't think it is right. I am thinking if I should scaffold the date with all AP record, that is huge calculation. Can anyone help me out?
Thank you in advance!
Hi @babyjb123
please try
=
SUMX (
FILTER (
CALCULATETABLE ( 'Account Payable History', ALL ( 'Date' ) ),
'Account Payable History'[Invoice Date] <= MAX ( 'Date'[Date] )
&& 'Account Payable History'[Check Date] >= MAX ( 'Date'[Date] )
),
'Account Payable History'[Payment Amount]
)
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!