Opening and closing balance calculations

[RanjanThammaiah]

Hi Team,

i would like to calculate the opening and closing balance based on the below table.

Can someone help me how can I do it.

Table1

Period	Month	FTE
1	Jul	500

Table2

Period	Month	New Joiner	Exit
1	Jul	5	4
2	Aug	10	5
3	Sep	8	9

result i want to arrive.

1. July opening balance will be the 500(Table1)+5 New joiner(table2)+4Exit(Table2)=Closing Balance.
2. Aug month Opening balance has to come from July month Closing balance.

July	Aug	Sept
Opening balance	New Joiner	Exit	Closing balance	Opening balance	New Joiner	Exit	Closing balance	Opening balance	New Joiner	Exit	Closing balance
500	5	4	501(100+4-4)	501	10	5	506(501+10-5)	506	8	9	505(506+8-9)

Any help is greatly appreciated.

Regards,

Ranjan

[v-shex-msft · Accepted solution]

Hi @RanjanThammaiah,

You can try to use the following measure formula if they suitable for your requirement:

Closing Balance =
VAR currDate =
    MAX ( DateTable[Date] )
VAR initAmount =
    SUMX (
        FILTER ( ALLSELECTED ( Table1 ), DATEVALUE ( [Month] & "/1" ) <= currDate ),
        [FTE]
    )
VAR rolling =
    CALCULATE (
        SUM ( Table2[New Joiner] ) - SUM ( Table2[Exit] ),
        FILTER ( ALLSELECTED ( Table2 ), DATEVALUE ( [Month] & "/1" ) <= currDate )
    )
RETURN
    initAmount + rolling

Opening Balance =
VAR currDate =
    MAX ( DateTable[Date] )
VAR initAmount =
    SUMX (
        FILTER (
            ALLSELECTED ( Table1 ),
            DATEVALUE ( [Month] & "/1" )
                < DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
        ),
        [FTE]
    )
VAR rolling =
    CALCULATE (
        SUM ( Table2[New Joiner] ) - SUM ( Table2[Exit] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            DATEVALUE ( [Month] & "/1" )
                < DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
        )
    )
RETURN
    initAmount + rolling

Regards,

Xiaoxin Sheng