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 calculate the Working Capital for a given end-date using this formula:

 

Step 2. My next step is to calculate the Cumulative Working Capital over the past 12 months. I have used the following formula with success tocalculate TTM (trailing 12 month) figures for EBITDA, COGS, and other Income Statement activity. However, all Income Statement Activity utilizes the Amount column in my data, whereas Balance Sheet activity must utilize a measure called Running Total, in order to see the ending balance of the activity for a given period end. Since my formula to calculate Working Capital at a given period end is based on a running total, it seems this formula doesn't work. Currently, when I apply this formulas, both "Working Capital" and "Working Capital - TTM" are the same exact figure.

 

For reference, here is the formula I use forRunning Total.

 

 

Step 3. Once I have a correct TTM Working Capital figure, I will add an Average function.

 

Example of expected output:

Month EndEnding Working CapitalTTM Working CapitalAvg TTM Working Capital
6/30/2020      3,935  
7/31/2020      4,378  
8/31/2020      4,389  
9/30/2020      3,621  
10/31/2020      3,462  
11/30/2020      2,223  
12/31/2020      1,868  
1/31/2021      1,896  
2/28/2021      4,321  
3/31/2021      3,200  
4/30/2021      3,327  
5/31/2021      3,952     40,572        3,381
6/30/2021      1,885     38,522        3,210
7/31/2021      2,859     37,003        3,084
8/31/2021      4,427     37,041        3,087
9/30/2021      4,709     38,129        3,177
10/31/2021      2,287     36,954        3,080
11/30/2021      4,716     39,447        3,287
12/31/2021      4,066     41,645        3,470
1/31/2022      3,454     43,203        3,600
2/28/2022      1,426     40,308        3,359
3/31/2022      3,944     41,052        3,421
4/30/2022      4,004     41,729        3,477
5/31/2022      3,199     40,976        3,415
6/30/2022      3,024     42,115        3,510

 

 

I appreciate your help.

 

 

 

 

 

 

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.

5 Replies

  • 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.

  • Anonymous's avatar
    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

    • LobsterLegend's avatar
      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(
          '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.