Forum Discussion

katemarkoska's avatar
katemarkoska
Frequent Visitor
2 years ago
Solved

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

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 G...
Need Help
  • katemarkoska's avatar
    katemarkoska
    2 years ago

    Hi Anonymous 
    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]
)

     



Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
katemarkoska
Frequent Visitor
2 years ago

Hi Anonymous 
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]
)