Measure that is dependent on previous value

[crazysportfan41]

Hi,

Relatively new user here. I've seen similar situations but nothing that is exactly like mine. Basically I have a data point where the only way to get the next value is to do a formula referencing the previous date's value. I included a sample screenshot in Excel, I have the data in categories A, B, and C via measures no problem. But the Cumulative value is multiplying the previous day's Cumulative value by a function of category C, and then adding that to a function of categories A and B. I cannot figure out how to calculate the Cumulative values. Ideally I'd like to calculate each of these for different teams and be able to change the dates as well with the calculation still accurate. The first value in whatever date range is equal to category C, as can be seen in the first value in the Excel table. Any idea on how to do this? Doing this in Excel is very simple, but I can't figure out how to do this in Power BI.

Thanks.

[v-yuezhe-msft]

@crazysportfan41,

You can use LN() and EXP() function or PRODUCTX() function to achieve above recursive calculation, there is a blog for your reference.

https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

Regards,
Lydia

[crazysportfan41]

Thanks Lydia. I've seen that blog but unfortunately I still can't get this to work. I actually used that exact blog to calculate another field, the difference though is that field was multiplied by the same value so productx worked. The problem I'm asking about in this issue is that the next value relies on the previous value, which relies on a sum of the current value.

I've attached a screenshot of the problem I'm seeing. Let's looks at period 4, for example. The column that I'm having problems with is column E only, all the other ones I can figure out. For the value in column E for period 4, that is asking for the value in column B for period 4 (no problem) times the value in column F for period 3 (problem). The value in column F for period 3 is the sum of columns D and E for period 3. So in other words, the value in column E for period 4 relies on a summation of the 2 other values, one of which is its own value for the prior period. Complicated, I definitely know.

The blog's example gives something like if if all the values to be multiplied are in one column, such as column B. That is easy, since I could theoretically get the value of period 6 without even knowing the values of periods 1-5. But for this issue, I can't figure out a way to do this since every period's value absolutely relies on the previous period's value.

Any other thoughts? I've been trying this for at least 4 days now and haven't gotten anywhere.

Thanks.

[crazysportfan41]

Any thoughts on this? I've been trying to figure this out for like 2 weeks now and cannot figure anyway around it whatsover, I'm desperate for any sort of solution.

Thanks.

[Anonymous]

Hello, could you figure out how to solve this? Im dealing with a very similar problem.