Calculating a running total based on the previous row

[pdoak]

I need to calculate a running total that is based upon previous cumulative totals but I keep getting a circular reference error. I have constructed a simple example to show the problem in Excel.

I have actual data up to 31/3/24 for cash movements each day. From 1/4/24, the cash movements are forecast based on an estimated revenue number for the month and anticipated costs.

 

The following measure transtorms the monthly revenue into daily revenue:

Rev FCast = 
var DaysInContext = CALCULATE( COUNTROWS( Dates ), Dates[Day of Week]<>5 && Dates[Day of Week] <> 6 )

VAR DaysInMonth = CALCULATE(COUNTROWS( Dates ), REMOVEFILTERS(Dates), VALUES( Dates[Year-Month] ), Dates[Day of Week]<>5 && Dates[Day of Week] <> 6  )
VAR MonthlyRevenueAmounts = CALCULATE( SUM('Revenue FCast'[Rev Est] ) , TREATAS( VALUES( Dates[Year-Month] ), 'Revenue FCast'[Year Month] ))

RETURN
IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Year-Month] )),
    IF( NOT(OR( Min( Dates[Day of Week]) =5, Min( Dates[Day of Week]) = 6)),
    DIVIDE( DaysInContext, DaysInMonth, 0 ) * MonthlyRevenueAmounts,
    0
    ),
    MonthlyRevenueAmounts
)

A further measure calculates the actual and estimated calv movements:

Total ActFCast = SUM( RevAct[Revenue] ) + SUM( Costs[Costs] ) + [Rev FCast]

 

If the cash cumulative balance turns negative, a borrowing requirement is required to bring the cash balance back to zero for the day. The above table shows a sample for April 2024. The column that I can't calculate in Power Bi is the final column in the above table showing the running total including the daily borrowing. Everything I try says that I have a circular reference.

 I have a pbix file that I have created as an example but if I drop it onto the post it says that the file type is not supported.

[v-jtian-msft]

Hi,@pdoak 
I am glad to help you.
According to your description, you want to calculate a running total based on previous cumulative totals, but are running into issues with circular references.
Below I have my test results, I created the data as I understood it as I did not have access to the original data.
I first created the test data based on the code you provided and used the measure, unfortunately I did not have a problem, I think the test data I created was a bit simple and had fewer relationships between the tables, so it did not reproduce your problem!
If you can provide relevant non-sensitive data, it would be helpful to solve your problem.

The relationship between the tables is as follows:

As for your circular reference problem, it should be that one of the calculations in the table depends on another calculation.

(possibly between two measures).

By inference, the problem may be in the measure:[Rev FCast].
The above is my guess, you can refer to it

Here's the data and calculations I constructed myself based on the functionality you describe
1. Cum Bal
measure : Cum Bal
implements an accumulation process for Cash M'ment.

Cum Bal = 
VAR Date_=MAX('Test'[Date])
VAR Current_total=CALCULATE(
    SUM(Test[Cash M'ment]),FILTER(
        ALL('Test'),'Test'[Date]<=Date_)
)
RETURN Current_total

2.Borrowing

Borrowing = 
IF(
    'Test'[Cum Bal]<0,
    ABS('Test'[Cum Bal]),
    0
)

If cash accumulation to current date is negative, borrowing is required, otherwise result is 0 (no borrowing required)

Cum inc Borrowing = 
IF(SELECTEDVALUE(Test[Date])>=DATE(2024,4,1),
'Test'[Cum Bal]+'Test'[Borrowing],
'Test'[Cum Bal])

The above test results are my own understanding, please correct me if I am wrong.

Can you describe to me the definition of each field in the table?

I am looking forward to your reply.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[pdoak]

Hi @v-jtian-msft . Thank you for taking the time to respond.

Unfortunately, your solution does not solve my problem as your solution does not include the borrowed value in the cumulative column which is what I need.  I have prepared a pbix file with sample data corresponding to the table in my original post which I would like to share to make it easier for you to understand and solve my problem. When I try and drop the pbix file onto the page I get an error message saying that the file type is not supported. I see that you were able to attache pbix files to your post; how do I do that?

In the meantime, if you look at my table for 5/4/24, you will see that a borrowing requirement of 863.64 is required which brings the cumulative total to zero on that day.  Subsequent cash movements are then added to that cumulative total.

[v-jtian-msft]

Hi,@pdoak .
Thank you for the prompt correction.
Based on the fact that you are not able to upload pbix files, I am very sorry to hear that you may not be able to upload files at this time for users, don't worry, here is my test data, You can refer to it to see if it meets your expectations.
I summed up the Cash M'ment,borrowing columns separately and then created a new measure to sum up

Cum Bal = 
VAR Date_=MAX('Test'[Date])
VAR Current_total=CALCULATE(
    SUM(Test[Cash M'ment]),FILTER(
        ALL('Test'),'Test'[Date]<=Date_)
)
RETURN Current_total

Cum  Borrowing = 
VAR Date_=MAX('Test'[Date])
VAR Cum_Borrowing = CALCULATE(
    SUM('Test'[Borrowing]) ,FILTER(
        ALL(Test),'Test'[Date]<=Date_)
        )
RETURN Cum_Borrowing 

Cum inc borrowing = [Cum  Borrowing]+[Cum Bal]

For your pbix file not being able to upload, you can refer to the following answer
URL：How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[pdoak]

Hi @v-jtian-msft , thank you for amending your answer. Unfortunately, it is not correct as the borrowing requirement is not being calculated correctly in your data. If the cumulative balance never goes below zero, no borrowing requirement is necessary. 

My sample data is here  which is the sample data in the table in my original post in a pbix file. In my data, the cumulative balance does not fall below zero until 5th April. On this day the borrowing requirement should be 863.64 bringing the cumulative cash balance to zero including the addition of the borrowing requirement. 
The next day, 6th April, a negative cash movement of 100 is experienced with the borrowing requirement being 100 and again the cumulative cash balance should be zero.
On 7th April, a positive cash flow of 809.09 occurs bringing the cumulative cash balance to 809.09. The next day that a borrowing requirement is required is 12th April. 

Basically, I want to be able to reproduce in Power Bi the final two columns in the table I posted in the original post. 

Many thanks