Forum Discussion

RobertSlattery's avatar
RobertSlattery
Icon for Responsive Resident rankResponsive Resident
9 years ago

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