Running Total that includes calculations based on prior amount

[CanadaMGB]

Is it possible to produce a running total in DAX that includes calculations based off the previous running total balance? My thinking is no, but after researching it I couldn't find a definitive answer, and so I'm throwing it out there in case I'm completely out to lunch and it is possible.

As a simplified example,  the image below from excel shows a running count of customers based on the estimated % of new and lost (churn) customers each month. The question posed to me is whether i could produce this in Power BI if given only an opening amount, New % and Churn %. The New and Churn amount are calculated as the product of that month's % times the previous month's running total, and I cannot come up with a way of calculating that without causing circular dependancy errors.

Thanks in advance!

[v-caliao-msft · Accepted solution]

@CanadaMGB,

I have tested it on my local by using the sample data below.

Then create a calculated column
Rate = IF(ISBLANK(Table2[Churn%]),400,1-Table2[Churn%]+Table2[New%])

And create a measure
Measure 2 = CALCULATE(PRODUCTX(Table2,Table2[Rate]),FILTER(ALL(Table2),Table2[Date]<=MAX(Table2[Date])))

Result

Regards,

Charlie Liao

[MattAllington]

Nice Charlie

[v-caliao-msft]

[CanadaMGB]

@v-caliao-msft  That's awesome, thanks Charlie!

[MattAllington]

I'm not sure if this can be done or not.  If so, it would certainly be complex.  But it seems to me that this requirement is a bit back to front anyway.  The % churn and new numbers presumably are generated based on the actual numbers of new and churn, not the other way around.  Stated another way, it seems to me that somewhere there must be the raw data of the number of new and churn customers that were used to generate these percentages.  This is what would ideally be loaded.

[CanadaMGB]

thanks @MattAllington, good to know I'm at least not missing something obvious. And yes I definitely oversimplified my example, as you pointed out it is a bit backwards thinking as stated. The actual data I'm working with is part of a forecast, hence why the rates are provided and not the raw data on the counts.

By the way, attended your session on evaluation context at Data Insights last week, very helpful!

[MattAllington]

Glad ,my session was helpful.  I haven't given up on your problem being doable.  Maybe if I get bored doing my real work this week I will take another look.  I am thinking a calculated column would be better in this example.  You may remember I said "don't use a calc column unless you know why".  In this case a measure would be quite heavy at run time as each calculation would become incrementally longer to execute.  Precalculating the numbers and storing in a table would seem to make sense to me.