How do I create a running total measure of a running total measure?

[paiello1]

I am trying to create a measure (Preferred Return Due to Date) that is a running total of another running total measure (Preferred Return Due Quarterly). I have searched this site and other sites for solutions, but I cannot figure it out. Any help would be appreciated. Please excuse the formatting of this post. I was getting errors when I tried to post code, and I could not figure out how to attach files.

Here are the measures I used:

Capital Contributions to Date =

CALCULATE (

    Transactions[Transaction Amount],

    ALL( 'Date' ),

    Transactions[Date] <= MAX( 'Date'[Date] ),

    Transactions[Attribute] = "Contribution Amount"

)

Returned Capital to Date =

CALCULATE (

    Transactions[Transaction Amount],

    ALL( 'Date' ),

    Transactions[Date] <= MAX( 'Date'[Date] ),

    Transactions[Attribute] = "Return of Capital"

)

Unreturned Capital to Date =

[Capital Contributions to Date] - [Returned Capital to Date]

Preferred Return Due Quarterly =

.07 * [Unreturned Capital to Date] / 4

This is the measure giving me problems:

Preferred Return Due to Date =

CALCULATE (

    SUMX(Transactions,[Preferred Return Due Quarterly]),

    ALL ('Date'),

    Transactions[Date] <= MAX('Date'[Date])

)

 Here is a screenshot of my transactions table:

Here is a screenshot of the matrix visualization:

The Preferred Return Due to Date should be a running total of Preferred Return Due Quarterly.

[v-junyant-msft]

Hi @paiello1 ,

Please try this way:
I used Power Query to add an indexed column starting at 1 to the data table:

Then I use this DAX to create a new column:

Preferred Return Due to Date = 
CALCULATE(
    SUM('Table'[Preferred Return Due Quarterly]),
    FILTER(
        ALL('Table'),
        'Table'[Index] <= EARLIER('Table'[Index])
    )
)

The results are as follows:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paiello1]

@v-junyant-msft Unfortunately, that approach does not work. The table you added an index to is not a table. It is a matrix visualization (i.e. the output of the measures I created). So, I cannot add an index to it. Any other thoughts would be welcome as I am still stuck on it.

[Data-estDog]

Could you somehow splice in your expected result on the last screenshot.

[paiello1]

@Data-estDog Thank you for considering this issue. Here is the table with the desired results for Preferred Return Due to Date.

QuarterInCalendar	Capital Contributions to Date	Returned Capital to Date	Unreturned Capital to Date	Preferred Return Due Quarterly	Preferred Return Due to Date
Q1 2013
Q2 2013	4,200,000		4,200,000	73,500	73,500
Q3 2013	4,200,000		4,200,000	73,500	147,000
Q4 2013	4,200,000		4,200,000	73,500	220,500
Q1 2014	4,200,000		4,200,000	73,500	294,000
Q2 2014	4,200,000		4,200,000	73,500	367,500
Q3 2014	4,200,000		4,200,000	73,500	441,000
Q4 2014	4,200,000		4,200,000	73,500	514,500
Q1 2015	4,200,000		4,200,000	73,500	588,000
Q2 2015	4,200,000		4,200,000	73,500	661,500
Q3 2015	4,200,000		4,200,000	73,500	735,000
Q4 2015	4,200,000		4,200,000	73,500	808,500
Q1 2016	4,200,000	250,000	3,950,000	69,125	877,625
Q2 2016	4,200,000	250,000	3,950,000	69,125	946,750
Q3 2016	4,200,000	250,000	3,950,000	69,125	1,015,875
Q4 2016	4,200,000	250,000	3,950,000	69,125	1,085,000
Q1 2017	4,200,000	250,000	3,950,000	69,125	1,154,125
Q2 2017	4,200,000	250,000	3,950,000	69,125	1,223,250
Q3 2017	4,200,000	250,000	3,950,000	69,125	1,292,375
Q4 2017	4,200,000	250,000	3,950,000	69,125	1,361,500
Q1 2018	4,200,000	250,000	3,950,000	69,125	1,430,625
Q2 2018	4,200,000	250,000	3,950,000	69,125	1,499,750
Q3 2018	4,200,000	250,000	3,950,000	69,125	1,568,875
Q4 2018	4,200,000	250,000	3,950,000	69,125	1,638,000
Q1 2019	4,200,000	250,000	3,950,000	69,125	1,707,125
Q2 2019	4,200,000	250,000	3,950,000	69,125	1,776,250
Q3 2019	4,200,000	250,000	3,950,000	69,125	1,845,375
Q4 2019	4,200,000	250,000	3,950,000	69,125	1,914,500
Q1 2020	4,200,000	250,000	3,950,000	69,125	1,983,625
Q2 2020	4,200,000	250,000	3,950,000	69,125	2,052,750
Q3 2020	4,200,000	250,000	3,950,000	69,125	2,121,875
Q4 2020	4,200,000	250,000	3,950,000	69,125	2,191,000
Q1 2021	4,200,000	250,000	3,950,000	69,125	2,260,125
Q2 2021	4,300,000	250,000	4,050,000	70,875	2,331,000
Q3 2021	4,300,000	250,000	4,050,000	70,875	2,401,875
Q4 2021	4,300,000	250,000	4,050,000	70,875	2,472,750
Q1 2022	4,400,000	250,000	4,150,000	72,625	2,545,375
Q2 2022	4,400,000	250,000	4,150,000	72,625	2,618,000
Q3 2022	4,500,000	250,000	4,250,000	74,375	2,692,375
Q4 2022	4,500,000	250,000	4,250,000	74,375	2,766,750
Q1 2023	4,500,000	250,000	4,250,000	74,375	2,841,125
Q2 2023	4,500,000	250,000	4,250,000	74,375	2,915,500
Q3 2023	4,500,000	250,000	4,250,000	74,375	2,989,875
Q4 2023	4,500,000	250,000	4,250,000	74,375	3,064,250
Q1 2024	4,500,000	250,000	4,250,000	74,375	3,138,625
Q2 2024	4,500,000	250,000	4,250,000	74,375	3,213,000
Q3 2024	4,500,000	250,000	4,250,000	74,375	3,287,375
Q4 2024	4,500,000	250,000	4,250,000	74,375	3,361,750
Q1 2025	4,500,000	250,000	4,250,000	74,375	3,436,125
Q2 2025	4,500,000	250,000	4,250,000	74,375	3,510,500
Q3 2025	4,500,000	250,000	4,250,000	74,375	3,584,875
Q4 2025	4,500,000	250,000	4,250,000	74,375	3,659,250