Help to bypass circular dependency (need to calculate 2 things one from each other)

[olivierhbh]

Hello all,

I need to calculate stocks, here is a simplified set of data:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]id[/TH]
[TH]year[/TH]
[TH]month[/TH]
[TH]prod[/TH]
[TH]start[/TH]
[TH]start_calculated[/TH]
[TH]end[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]2001[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001[/TD]
[TD]12[/TD]
[TD]74[/TD]
[TD] [/TD]
[TD]58[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2002[/TD]
[TD]1[/TD]
[TD]86[/TD]
[TD] [/TD]
[TD]132[/TD]
[TD]218[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2002[/TD]
[TD]2[/TD]
[TD]56[/TD]
[TD] [/TD]
[TD]218[/TD]
[TD]274[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2002[/TD]
[TD]3[/TD]
[TD]91[/TD]
[TD] [/TD]
[TD]274[/TD]
[TD]365[/TD]
[/TR]
</tbody>[/TABLE]


I want to calculate the initial stock and the final stock of each month, the initial stock is the final stock of the previous month and the final stock equals initial stock + production.

The column id to the column start represent the database. There is one row per month.

I need to calculate the start_calculated and end data.

start_calculated should be equal to start if there is something, else to the value in the field end of the previous month.

end should sum start_calculated and prod.

The circular dependency is obvious, I use start_calculated to calculate end and end to calculate start_calculated.

Could you help me find a workaround to achieve this result without using Power Query or editing the initial database?

Many thanks in advance for your help.

Olivier.

 

[Ozeroth]

Hi Olivier,

You can do it using cumulative sums.
The logic I've followed is:

• start_calculated = Most recent start value on or before current row + Sum of prod starting from most recent row with a start value and ending before current row
• end = start_calculated + Prod on current row

This is my version of the resulting DAX for the calculated columns (I've called table Table1):

[B]start_calculated =
[COLOR=#0000ff]CALCULATE (
    VALUES ( Table1[start] ),
    ALL ( Table1 ),
    CALCULATETABLE (
        LASTNONBLANK ( Table1[id], CALCULATE ( VALUES ( Table1[start] ) ) ),
        ALL ( Table1 ),
        FILTER ( ALL ( Table1[id] ), Table1[id] <= EARLIER ( Table1[id] ) )
    )
)[/COLOR][/B]
+
[B][COLOR=#006400]CALCULATE (
    SUM ( Table1[prod] ),
    ALL ( Table1 ),
    FILTER (
        ALL ( Table1[id] ),
        Table1[id] < EARLIER ( Table1[id] )
            && Table1[id]
                >= CALCULATE (
                    LASTNONBLANK ( Table1[id], CALCULATE ( SUM ( Table1[start] ) ) ),
                    ALL ( Table1 ),
                    FILTER ( ALL ( Table1[id] ), Table1[id] <= EARLIER ( Table1[id], 2 ) )
                )
    )
)[/COLOR][/B]

[B]end = [/B]Table1[start_calculated]+Table1[prod]

Blue = Most recent start value on or before current row.
Green = Sum of prod starting from most recent row with a start value and ending before current row

 

[olivierhbh]

Hi Ozeroth,

Thanks a lot for your answer, this works perfectly with the batch I provided however it can't calculate on my whole set of data of about 25K rows.
I'm on excel 2010 32 bits.
I'll try to improve the formula by the end of the week so it can work but if you have optimization ideas that would be awesome.

Olivier.

 

[olivierhbh]

I could improve the first part of the sum to get the corresponding start value but I still need to optimize the second member which is:

CALCULATE (
    SUM ( Table1[prod] ),
    ALL ( Table1 ),
    FILTER (
        ALL ( Table1[id] ),
        Table1[id] < EARLIER ( Table1[id] )
            && Table1[id]
                >= CALCULATE (
                    LASTNONBLANK ( Table1[id], CALCULATE ( SUM ( Table1[start] ) ) ),
                    ALL ( Table1 ),
                    FILTER ( ALL ( Table1[id] ), Table1[id] <= EARLIER ( Table1[id], 2 ) )
                )
    )
)

Any Ideas?

 

[olivierhbh]

Got it!!

Here is the final one:

=CALCULATE (
    LASTNONBLANK (
        Table1[start],
        CALCULATE ( VALUES ( Table1[start] ) )
    ),
    ALL ( Table1),
    Table1[id] = EARLIER ( Table1[id] )
)
    + CALCULATE (
        SUM(Table1[prod] ),
        FILTER (
            ALL ( Table1),
            Table1[id] = EARLIER ( Table1[id] )
                && Table1[date_key] < EARLIER ( Table1[date_key] )
        )
    ),2)

date_key is [year]&format([month], "00").

Thanks for your inspiration Ozeroth!

 

[Ozeroth]

No worries - yes it does look like it needs some optimisation.

Will there always be a single 'start' value at index 1? Or will there be multiple start values scattered through the table?

The main complication with the original formula was allowing for the possibility of a start value on any row.

 

[Ozeroth]

Sorry missed your 'got it' post - that's good. So you're doing the calc over dates for each [id] - your formula that makes more sense now - good work.

 

[olivierhbh]

No problem, thanks for your help!