Forum Discussion

LobsterLegend's avatar
LobsterLegend
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's avatar
    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.
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

I cannot understand your requirement.  A good start would be to create an MS Excel file with formulas in place to show how you arrive from the raw data to the expected result.  Show the problem there clearly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.