Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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=f50uf91t9oih7rtdxqb7722ix&st=keo6w1fd&dl=0
The below table shows the values that I expect to calculate.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

     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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

       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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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