factor cumulative balance for different relationship manager

[Anonymous]

I have three table named balance, datetable, rm mapping and rm master in power bi. Balance have disbursement and collection amount based on clients and it has dates also. Datetable is a calendar table . Rm master has unique rm names and codes. Rm mapping table has rm names for clients and last change date.

In Rm mapping table some clients were assigned to one relationship manager previously and now are assigned to different relationship manager(rm).

Now we want to calculate cumulative balance(disbursement -collection) based on dates from datetable table .we want that if suppose rm a had client b till March and now rm c is assigned to client b , balance for b will show for rm a till march and then from april balance for b will show for rm c but march values will add on april and so on as the calculation.

We need to create a measure in power bi to calculate the balance.

[MargusMartsepp]

This measure will take into account the disbursement and collection amounts, the date of change in RM assignment, and the date for which you want to calculate the cumulative balance.

This measure assumes that your 'Balance' table has a column named 'ClientID', 'Disbursement', and 'Collection'. Replace these with your actual column names

Cumulative Balance = 
VAR CurrentDate = MAX('DateTable'[Date])
VAR CurrentClients = VALUES('Balance'[ClientID])
RETURN
SUMX(
    CurrentClients,
    VAR CurrentClient = [ClientID]
    VAR LastRMChangeDate = CALCULATE(MAX('RM Mapping'[Last Change Date]), 'RM Mapping'[ClientID] = CurrentClient)
    VAR CurrentRM = CALCULATE(MAX('RM Mapping'[RM Name]), 'RM Mapping'[ClientID] = CurrentClient, 'RM Mapping'[Last Change Date] <= CurrentDate)
    VAR Disbursement = CALCULATE(SUM('Balance'[Disbursement]), 'Balance'[ClientID] = CurrentClient, 'Balance'[Date] <= CurrentDate, 'RM Mapping'[RM Name] = CurrentRM)
    VAR Collection = CALCULATE(SUM('Balance'[Collection]), 'Balance'[ClientID] = CurrentClient, 'Balance'[Date] <= CurrentDate, 'RM Mapping'[RM Name] = CurrentRM)
    RETURN
    Disbursement - Collection
)

Explanation:

1. CurrentDate: Fetches the maximum date from your 'DateTable', which should be the date for which you want to calculate the cumulative balance.
2. CurrentClients: Creates a table of unique client IDs present in the 'Balance' table.
3. LastRMChangeDate: For each client, fetches the last date when the RM was changed.
4. CurrentRM: Determines the RM assigned to the client as of the current date.
5. Disbursement and Collection: Calculates the total disbursement and collection for each client up to the current date, considering the RM assigned to them.
6. SUMX: Iterates over each client to calculate the cumulative balance (Disbursement - Collection).

[Anonymous]

Thank you for the solution but it is not coming properly. The values are coming as negative.