How to Calculate TTM Purchase Involving Date from Two Tables Balance Sheet and Income Statement

[ThangNT]

I want to calculate Purchase based on the formula Purchase = Ending Inventory - Beginning Inventory + COGS và TTM Purchase. I have written some DAX formula as follows:
Beginning Inventories =
CALCULATE(
[Ending Inventories],
PREVIOUSQUARTER('Date'[Date Key])
)
Ending Inventories =
CALCULATE(
[BalanceSheetValues],
Accounts[Account] = "Inventories"
)
COGS =
CALCULATE(
[QuarterlyIncomeStatementValues],
Accounts[SubAccount] = "Cost of Goods Sold"
) * -1
Purchase = Ending Inventories - Beginning = COGS
TTM Purchase =
CALCULATE(
SUMX(
IncomeStatement,
[Purchase]
),
DATESINPERIOD(
'Date'[Date Key],
Max('Date'[Date Key]),
-4,
QUARTER
),
FILTER(
All('Date'),
Max('Date'[Date Key]) > Date(2015,9,30)
)
)

I've identified the issue: the Purchase measure refers to two tables, BalanceSheet and IncomeStatement, while the SUMX function only operates over a single table. As a result, when I use the SUMX function to iterate over the IncomeStatement, the value of TTM Purchase ends up being the same as the value of TTM COGS. However, I'm still struggling to find a solution.

https://www.dropbox.com/scl/fi/ib8wnknxpglr7dfrfzjrr/NFC_Company-Analysis.pbix?rlkey=f50uf91t9oih7rt...
The below table shows the values that I expect to calculate.

[ThangNT]

I attempted a different approach to calculate TTM Purchase using the formula: TTM Ending Inventory - TTM Beginning Inventory + TTM COGS. The formulas for TTM Ending Inventory and TTM COGS work perfectly, but the formula for TTM Beginning Inventory does not return the correct values. I've tried various formulas as follows:
TTM Beginning Inventory =
SUMX(BalanceSheet, [Beginning Inventories])
It shows no values.

TTM Beginning Inventories =
CALCULATE(
[Ending Inventories],
FILTER(
ALL('Date'),
'Date'[Date Key] < MIN('Date'[Date Key])
)
)
It shows cumulative Beginning Inventories from 2014/12/31.

TTM Beginning Inventory =
CALCULATE(
[Beginning Inventories],
DATESINPERIOD(
'Date'[Date Key],
MAX('Date'[Date Key]),
-4,
QUARTER
),
FILTER(
ALl('Date'),
MAX('Date'[Date Key]) > Date(2015,9,30)
)
)

TTM Beginning Inventories at 2015/12/31 is equal to Beginning Inventories at 2014/12/31

[v-rzhou-msft · Accepted solution]

Hi @ThangNT ,

 I think your issue should be caused that the Date table is not continuous. Here I suggest you to update your meausre as below.

Beginning Inventories =
CALCULATE (
    [Ending Inventories],
    FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Column] = MAX ( 'Date'[Column] ) - 1 )
)

TTM Beginning Inventory =
IF (
    MAX ( 'Date'[Date Key] ) > DATE ( 2015, 9, 30 ),
    SUMX (
        FILTER (
            ALLSELECTED ( 'Date' ),
            'Date'[Column] <= MAX ( 'Date'[Column] )
                && 'Date'[Column]
                    >= MAX ( 'Date'[Column] ) - 4
        ),
        [Beginning Inventories]
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[ThangNT]

I have got another solution that seems to be simpler.  The DAX formulas for Purchase and TTM Purchase have been modified like that

Purchase =

SUMX(

    SUMMARIZE(

        'Date',

        'Date'[Year],

        'Date'[Quarter]

    ),

    [Ending Inventories] - [Beginning Inventories] + [COGS]

)

and

TTM Purchase =

VAR     Purchase =

CALCULATE(

    [Purchase],

    DATESINPERIOD(

        'Date'[Date Key],

        Max('Date'[Date Key]),

        -4,

        QUARTER

    ),

    FILTER(

        All('Date'),

        Max('Date'[Date Key]) > Date(2015,9,30)

    )

)

Return

Purchase