Calculate the sum of payment amount between to dates

[babyjb123]

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!

[tamerj1]

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]
)