Circular Measure Problem

[dansc0tt]

Hi,

I'm trying to build a model that calculates profit after it has been adjusted for prior operating losses. In the example below you can see that operating profit in period 2 is a loss of $50. This loss is then carred forward and offsets profit in period 3 and period 4 (see adj profit which is the number i am trying to get to using DAX).

I think i can achieve this using a calculated column approach but really looking for any good ideas on how i might achieve this using measures (trying to avoid additional tables if possible). The circularity involved here has me stumped!

Appreciate any thoughts.

Dan

[v-sihou-msft]

@dansc0tt

I can't understand why the third row is not -25. 

For your expected result, you can create a calculated column like below: 

Adj Progit =
IF (
    Table[Op Profit] - Table[Loss from prior eriod]
        <= 0,
    0,
    Table[Op Profit] - Table[Loss from prior eriod]
)

Regards,

[dansc0tt]

Thanks - this is helpful, but was looking for any thoughts on being able to do this with a measure rather than a calculated column. 

The third row is not -25 because you can only reduce your adj profit to minimum of zero in any period. The balance is then carried over to be used in future periods.

Dan

[mattbrice]

Which columns do you have calcualted already? Are they measures or calculated columns?  Or do you only have 'Period' and 'Op Profit' and need to calculate the rest?

[dansc0tt]

Thanks Matt - I have only 'Period' and 'Op Profit' of which 'Op Profit' is a measure.