Get child total with parent data along with date slicer

[Majidbhatti]

Hi,

I am kind of confused to how to solve this. Below is my table structure which needs to work with the DATE SLICER which can changed by the user.

Below is sample of what the output i am looking for. The OUTSTANDING AMOUNT is difference of TOTAL AMOUNT - SETTLED AMOUNT which is SUMED based on the "As of Date" slicer and shown in the Matrix as below

Moreover i want to show aging of the outstanding amount from Invoice date as below

[V-lianl-msft · Accepted solution]

Hi @Majidbhatti ,

Cancel the relationship between date table and other two tables.

Create a relationship based on Invoice No between the invoice transactions table and the invoice Settlements table.

Create measure like this:

Measure =
VAR slicer_date =
    SELECTEDVALUE ( 'Date table'[Date] )
VAR sum_settled =
    CALCULATE (
        SUM ( 'Invoice Settlements'[Settled Amount] ),
        FILTER (
            'Invoice Settlements',
            'Invoice Settlements'[SettleDate] <= slicer_date
        )
    )
RETURN
    SUM ( 'Invoice Transactions'[Total Amount] ) - sum_settled

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Majidbhatti]

Thanks @V-lianl-msft i had to keep the relationship between Transaction & Date to make this working otherwise it was pulling all the Transactions.

Any idea on how can i make the aging columns based on the "As of Date" dynamically as shown in the matrix.