Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year 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 = CALCU...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year 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
1 year 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
    1 year 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
      1 year 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