The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 @Anonymous
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]
)
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |