Map a LocalAccount to GlobalAccountPositive or GlobalAccountNegative based on the sum of the Amount

[katemarkoska]

Hi,
I'm trying to do a Balance Sheet report and I need to map Local Accounts to GlobalAccounts.
If the sum(Amount) for the LocalAccount of all data until the selected period is >=0 then map it to GlobalAccountPositive, if the sum(Amount) is less than 0, than map it to GlobalAccountNegative. 
I tried doing this with a calculated column in DAX

VAR Positive = VALUE('GenLedgEntries'[GlobalAccountPositive])

VAR Negative= VALUE('GenLedgEntries'[GlobalAccountNegative])

VAR maxdate=MAX('Date'[datekey])

VAR Val =  CALCULATE(SUM('GenLedgEntries'[Amount])/1000, ALL('Date'),'Date'[datekey]<=maxDate)

RETURN

IF(Val>=0, Positive, Negative)

but when I try to make a connection to my Hierarchy table I get an error: A circular dependency was detected: GenLedgEntries [MappedGlobal], a69b2817-4256-4faf-a817-c31dcd275159, GenLedgEntries [MappedGlobal].

Is there any way to do this in power query or with another approach with DAX?

[v-xinruzhu-msft]

Hi @katemarkoska 

You can try the following code.

Column =
VAR maxdate =
    MAX ( 'Date'[datekey] )
VAR Val =
    CALCULATE (
        SUM ( 'GenLedgEntries'[Amount] ),
        ALL ( 'Date' ),
        'Date'[datekey] <= maxDate
    )
RETURN
    IF (
        Val >= 0,
        'GenLedgEntries'[GlobalAccountPositive],
        'GenLedgEntries'[GlobalAccountNegative]
    )

 (Note:Please make sure the positive and negative column are  the calculated columns that depend on MappedGlobal column)

And you can refer to the following link.

Circular Dependency between Calculated Columns in ... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[katemarkoska · Accepted solution]

Hi @v-xinruzhu-msft 
Thanks, but this is almost the same code as mine and I still get the circular dependence error even with this code.
I have switched the datekey column from my Date table with the PostingDate from GenLedgEntries and it seems to work. Although it needs to be tested.

MappedGlobal = 
VAR TotalAmount=
CALCULATE(SUM('GenLedgEntries'[Amount])/1000,
FILTER(ALL('GenLedgEntries'),'GenLedgEntries'[PostingDate]<=MAX('GenLedgEntries'[PostingDate])))
RETURN
IF(
TotalAmount>=0, 
'GenLedgEntries'[GlobalAccountPositive],
'GenLedgEntries'[GlobalAccountNegative]
)