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)

 

 

Currently how the report looks like.

 

The first value of the Remaining Balance column should be a subtraction from the Total Money, and the next values will be a subtraction from the above value. Each Account Number will have its own cumulation.

 

The correct Remaining Balance numbers should be as follows:

 

I hope I explained it clearly.

If you have any questions, please let me know.

 

You can download a test workbook from here:

https://drive.google.com/file/d/1pR2FDw6ol6EOy5NHu7O8rBBY2C-yymoj/view?usp=sharing

 

 

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.

     

10 Replies

  • 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.