How can I create these calculated measures?

Hi Anonymous ,

This is a bit like a cumulative multiplication algorithm. power bi only supports the calculation of existing data, and cannot perform the next calculation based on its own calculation results. But we can optimize this algorithm.
Here is the logic of my calculation, you can refer to it.

assum a = factor * multiplier

p2 = b1 + a1

p3 = b2 + a2

p4 = b3 + a3

and

b2 = p2+b1

b3 = p3+b2

b4 = p4+b3

so ->

b2 = b1(1+a2)

b3 = b1(1+a2)*(1+a3)

b4 = b1(1+a2)*(1+a3)*(1+a4)

And the measures for [balance] and [profit] is:

balance =
VAR _b_s =
    SUMMARIZE (
        CALCULATETABLE (
            report,
            FILTER (
                ALLEXCEPT ( report, 'report'[version] ),
                [time] <= MAX ( 'report'[time] )
            )
        ),
        [version],
        [time],
        "fac",
            [multiplier] * SUM ( report[factor] ) + 1
    )
RETURN
    PRODUCTX ( _b_s, [fac] )

profit =
VAR _factor =
    SUM ( report[factor] )
VAR _a = [multiplier] * _factor
VAR _b_s =
    SUMMARIZE (
        CALCULATETABLE (
            report,
            FILTER (
                ALLEXCEPT ( report, 'report'[version] ),
                [time] < MAX ( 'report'[time] )
            )
        ),
        [version],
        [time],
        "fac",
            [multiplier] * SUM ( report[factor] ) + 1
    )
VAR _result =
    PRODUCTX ( _b_s, [fac] ) * _a
RETURN
    IF ( ISBLANK ( _result ), _a, _result )

Resutl:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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