Forum Discussion

elmurat's avatar
elmurat
Advocate I
6 months ago
Solved

Cumulative Difference from Another measure

Hello all,   Hope you are all having a great week.   Can you please help me figure out a calculation for the Cumulative Difference from another metric? (Remaining Balance column in my report)   ...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    6 months ago

    Hi elmurat ,

    Thanks for reaching out to Microsoft Fabric Community.

     

    The issue occurs because when multiple rows share the same Battle Date, there is no deterministic row order, which can cause movements to be combined instead of deducted sequentially.

     

    To handle this, I brought NameAccount into the Accounts_Bookings table, sorted the data by NameAccount, Battle Date, and DealName in Power Query, and added a TransactionIndex column to create a stable row order. The running balance then uses this index to ensure row by row subtraction within each account.

    Please find the measure below:

    Remaining Balance = 
VAR CurrentAccount =
    MAX(Accounts_Bookings[NameAccount])

VAR CurrentIndex =
    MAX(Accounts_Bookings[TransactionIndex])

VAR TotalCash =
    CALCULATE(
        SUM(Accounts_Cash[Total Cash]),
        Accounts_Cash[Account Number] = CurrentAccount
    )

VAR RunningMovement =
    CALCULATE(
        SUM(Accounts_Bookings[Balancito]),
        FILTER(
            ALL(Accounts_Bookings),
            Accounts_Bookings[NameAccount] = CurrentAccount
                &&
            Accounts_Bookings[TransactionIndex] <= CurrentIndex
        )
    )

RETURN
    TotalCash - RunningMovement

     

    I have attached a screenshot of the output and the PBIX file created using the sample CSV files. Please review and confirm if this aligns with your expected result.

     

GeraldGEmerick's avatar
GeraldGEmerick
Super User
6 months ago

elmurat Try something like the following DAX measure:

Remaining Balance 2 = 
VAR _TotalMoney = SUM( Accounts_Cash[Total Cash] )
VAR _BattleDate = MAX( Accounts_Bookings[Battle Date] )
VAR _AccountName = MAX( AccountName_AccountNumber[NameAccount] )
VAR _GQIDs = SELECTCOLUMNS( FILTER( 'AccountName_AccountNumber', [NameAccount] = _AccountName ), "_GQID", [GQID] )
VAR _Table = SUMMARIZE( FILTER( ALL( 'Accounts_Bookings' ), [Battle Date] <= _BattleDate && [GQID] IN _GQIDs ), Accounts_Bookings[DealName], Accounts_Bookings[Battle Date], AccountName_AccountNumber[NameAccount], "Movement", SUM( 'Accounts_Bookings'[Balancito] ) )
VAR _BalanceTable = ADDCOLUMNS( _Table, "RemainingBalance", _TotalMoney - SUMX( FILTER( _Table, [Battle Date] <= EARLIER( Accounts_Bookings[Battle Date] ) ), [Movement] ) )
VAR _Return = MAXX( FILTER( _BalanceTable, [Battle Date] = _BattleDate ), [RemainingBalance] )
RETURN _Return
  • elmurat's avatar
    elmurat
    Advocate I
    6 months ago

    Hi Gerald, thank you very much for your DAX. It's almost working.

    When I have 2 same battle dates, for two different DealNames, the 2 Movement amounts are being added up and subtracted from the Total Money, so the Remaining Balance is the same for two DealNames. I feel like the DealName should be included in the logic somewhere. Can you help me update the DAX, please?

     

     

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Community Support
      6 months ago

      Hi elmurat ,

      Thanks for reaching out to Microsoft Fabric Community.

       

      The issue occurs because when multiple rows share the same Battle Date, there is no deterministic row order, which can cause movements to be combined instead of deducted sequentially.

       

      To handle this, I brought NameAccount into the Accounts_Bookings table, sorted the data by NameAccount, Battle Date, and DealName in Power Query, and added a TransactionIndex column to create a stable row order. The running balance then uses this index to ensure row by row subtraction within each account.

      Please find the measure below:

      Remaining Balance = 
VAR CurrentAccount =
    MAX(Accounts_Bookings[NameAccount])

VAR CurrentIndex =
    MAX(Accounts_Bookings[TransactionIndex])

VAR TotalCash =
    CALCULATE(
        SUM(Accounts_Cash[Total Cash]),
        Accounts_Cash[Account Number] = CurrentAccount
    )

VAR RunningMovement =
    CALCULATE(
        SUM(Accounts_Bookings[Balancito]),
        FILTER(
            ALL(Accounts_Bookings),
            Accounts_Bookings[NameAccount] = CurrentAccount
                &&
            Accounts_Bookings[TransactionIndex] <= CurrentIndex
        )
    )

RETURN
    TotalCash - RunningMovement

       

      I have attached a screenshot of the output and the PBIX file created using the sample CSV files. Please review and confirm if this aligns with your expected result.

       

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Community Support
        6 months ago

        Hi elmurat ,

        Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.


        Thank you.