Circular dependency DAX

[Marcoss]

Hello everyone,

I would like to calculate for each month the value of D' as the sum of A', B' and C', based on the inputs: Start (only for the first month), A' (for the first month equals "Start"), B' and C'.

The problem is that I only know A' for the first month (equals "Start"), and for next months A' should come from D' from the previous month.

So in the example below for the second month it should be:
D' = 106 (instead of 110) + 4 + 6 = 116.

Do you have any ideas?

Month	Start	A	B	C
1/1/2023	100		2	4
2/1/2023			4	6
3/1/2023			6	8
4/1/2023			8	10
5/1/2023			10	12
6/1/2023			12	14

Start' = SUM('Table'[Start])

B' = SUM('Table'[B])

C' = SUM('Table'[C])

D' = [A'] + [B'] + [C']

A' =

VAR FirstMonth = [Start']

VAR NextMonths =

CALCULATE(

    [Start'],

    DATEADD(

        'Table'[Month],

        -1,

        MONTH

    )

)

+ [B'] + [C']

RETURN

IF(

    [Start'] <> BLANK(),

    FirstMonth,

    NextMonths

)

[Marcoss]

Thank you, but I also solved it in another way.

A' =

VAR FirstMonth = [Start']

VAR NextMonths =

CALCULATE(

    [Start'],

    ALL('Table'[Month])

)

+

CALCULATE(

    [B'],

    'Table'[Month] < MAX('Table'[Month])

)

+

CALCULATE(

    [C'],

    'Table'[Month] < MAX('Table'[Month])

)

RETURN

IF(

    FirstMonth <> BLANK(),

    FirstMonth,

    NextMonths

)

[ChiragGarg2512 · Accepted solution]

Hello @Marcoss,

Try this DAX for A':

A' = CALCULATE(FIRSTNONBLANKVALUE('Table (2)'[Month], [Start']), All())+CALCULATE(SUMX('Table (2)', 'Table (2)'[B]+'Table (2)'[C]), FILTER(ALL('Table (2)'), 'Table (2)'[Month]<max('Table (2)'[Month])))

Thank You.

Replace Table (2) with the table name.