Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Create a summary table filtered by columns in another table

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

1 Reply

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.