Create a summary table filtered by columns in another table

[Syndicate_Admin]

Hello

I have three tables:

• Process calendar, with columns,
  • Process month
  • Start date period
  • End date period
• Customer accounts, with columns
  • Client code
  • Move date
  • Type of movement: "Charged" or "Unpaid"
  • amount
• List of unpaid customers
  • Month of processing.
  • Client code
  • Unpaid invoices at the date of the process
  • Import unpaid invoices at the processing date

I want to create a calculated table that contains the columns:

• From the "list of customers with unpaid" table:
  • client code
  • process month
  • sum(amount unpaid invoices at the processing date)
• From the "customer accounts" table:
  • sum(amounts) whose "Movement type"="Cashed" and movement date>=Start date period and movement date <= end date period

Thank you very much for your help.

Javier

[v-angzheng-msft]

Hi, @Syndicate_Admin 

Based on what I got, create a calculate table like below:

Table =
SUMMARIZE (
    'List of Unpaid customers',
    [Client code],
    [process month],
    "sum1", SUM ( [unpaid invoices at the processing date] ),
    "sum2",
        CALCULATE (
            SUM ( [amouts] ),
            FILTER (
                "Customer accounts",
                [Type of movement] = "Cashed"
                    && [Move date] >= MAX ( 'Process calendar'[Start date period] )
                    && [Move date] <= MAX ( 'Process calendar'[end date period] )
            )
        )
)

 It is hard to build a real data model without sample data

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.