Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dax for Circular dependency

Hello All, 

I have a table 

Fiscal year

Allocations

Expenditure

2022

10

5

2023

20

2

2024

10

6

2025

25

7

 

i need to create 2 measure which are dependent on each other 

i need to calculate bal carryon and bal log 

bal carryon = last year bal log (we don’t have data prior to 2022 so we need to assume the 2022 bal carryon is 0 

and 

bal carryon = bal-exp+bal carryon 

Fiscal year

Allocations

Expenditure

bal carryon 

bal log 

2022

10

5

0

5

2023

20

2

5

23

2024

10

6

23

27

2025

25

7

27

45

 

Fiscal year 2022

bal carryon = 0 (we dont have data for pervious years so we consider it as 0)

bal log = 5 {10(2022 allocations)-5(2022 expenditure)+0 (2022 Bal Carryon)}

 

Fiscal Year 2023

Bal Carryon = 5 (this 2022 bal log)

bal log = 23 {20(2023 allocations)-2(2023 expenditure)+5(2022 bal log)}

 

Fiscal Year 2024

Bal Carryon = 23 (this 2023 bal log)

bal log = 27 {10(2024 allocations)-6(2024 expenditure)+23(2023 bal log)} and so on 

 


Thanks

Prashanth

Need Help
Tips and Tricks

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You can use

    Bal carry on =
VAR CurrentYear =
    SELECTEDVALUE ( 'Table'[Fiscal year] )
VAR Result =
    CALCULATE (
        SUMX ( 'Table', 'Table'[Allocations] - 'Table'[Expenditure] ),
        'Table'[Fiscal Year] < CurrentYear
    )
RETURN
    Result

    and

    Bal log =
VAR CurrentYear =
    SELECTEDVALUE ( 'Table'[Fiscal year] )
VAR Result =
    CALCULATE (
        SUMX ( 'Table', 'Table'[Allocations] - 'Table'[Expenditure] ),
        'Table'[Fiscal Year] <= CurrentYear
    )
RETURN
    Result