Forum Discussion
Power BI Filter expression using terms from related tables
I have an invoice table and a related Order table and I want to calculate the cumulative invoiced sales as well as the cumulative order intake. Because the current date is the invoice date, I am trying to sum orders with order date <= invoice date.
I haver a relationship from Order Number in the invoice table to Order Number in the Orders table.
The cumulative invoiced sales works fine...
Sales 01 Cumul = CALCULATE(
SUM('Invoice_Summary All'[Sales 01]),
FILTER(
ALLSELECTED('Invoice_Summary All'),
'Invoice_Summary All'[To] <= MAX('Invoice_Summary All'[To])
)
)
But I can't get the order intake to work. This is what I tried...
Order Intake Cumul = CALCULATE(
SUM('Invoice_Summary All'[Sales 01]),
FILTER(
ALLSELECTED('Order Headers All'),
'Order Headers All'[Order date] <= MAX('Invoice_Summary All'[To])
)
)
This tries to run for ages until it fails reporting insufficient memory.
1 Reply
- Eric_Zhang
Microsoft Employee
Could you post any sample data of those two tables and expected output?