Forum Discussion

Frequent Visitor

4 years ago

Solved

Trailing 12 Months Figure based on Running Total

I am attempting to calculate the Average Working Capital for my company over the past 12 months. The underlying table is all transactions in the general ledger. Step 1. I have been able to calcul...

Need Help

LobsterLegend
4 years ago
yingyinr,

Thank you for your detailed reponse. Unfortunately, this still yielded the same figure as the month-only Working Capital. Note I could *not* utilize the SUM function near the end of your formula string. The reason for this seems to be because my underlying figure, Working Capital, is itself a Measure, which itself is based on a Running Total Measure. Because I am attempting to calculate a TTM (trailing 12 month) figure for a Balance Sheet account, I found the following utilizing the PARALLELPERIOD function for every prior period needed was the only workable solution.

It's ugly, I know. And I'm sure there's a better solution. But this one works.

Z-FS - Working Capital - TTM =
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], 0, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -1, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -2, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -3, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -4, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -5, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -6, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -7, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -8, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -9, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -10, MONTH)
)
+CALCULATE(
'GL Account Transactions - Current & Archive'[Z-FS - Working Capital],
PARALLELPERIOD('DATE TABLE'[Calendar EndOfMonth], -11, MONTH)

From here I calculated the average value, and ultimately was able to derive the Working Capital Turnover, my desired financial ratio.

Anonymous

Not applicable

4 years ago

Hi LobsterLegend ,

You can create two measures as below to get the rolling 12 months values and average values...

TTM Working Capital = 
VAR _mmindate =
    CALCULATE ( MIN ( 'Table'[Month End] ), ALL ( 'Table' ) )
VAR MaxDate =
    SELECTEDVALUE ( 'Table'[Month End] )
VAR MinDate =
    EDATE ( MaxDate, -12 )
RETURN
    IF (
        DATEDIFF ( _mmindate, MaxDate, MONTH ) < 11,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[Ending Working Capital] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Month End] <= MaxDate
                    && 'Table'[Month End] > MinDate
            )
        )
    )

Avg TTM Working Capital = DIVIDE([TTM Working Capital],12)

Best Regards

Trailing 12 Months Figure based on Running Total.pbix43 KB

LobsterLegend

Frequent Visitor

4 years ago

yingyinr,

Thank you for your detailed reponse. Unfortunately, this still yielded the same figure as the month-only Working Capital. Note I could *not* utilize the SUM function near the end of your formula string. The reason for this seems to be because my underlying figure, Working Capital, is itself a Measure, which itself is based on a Running Total Measure. Because I am attempting to calculate a TTM (trailing 12 month) figure for a Balance Sheet account, I found the following utilizing the PARALLELPERIOD function for every prior period needed was the only workable solution.

It's ugly, I know. And I'm sure there's a better solution. But this one works.

Z-FS - Working Capital - TTM =

+CALCULATE(