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

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