Forum Discussion
Solved
Circular dependency DAX
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
)
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.
2 Replies
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.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]))RETURNIF(FirstMonth <> BLANK(),FirstMonth,NextMonths)
