Balance Sheet Running Balance sheet type/Account/Year/period

[dcormiernj]

I have searched and cant find something that works for me.  I have a table im calling balance sheet data and it is structured kind of like the paste below.  I am trying to calculate the ending balance for each balancesheettype/Account/Fiscalyear/fiscalperiod

The pseudo code would be the following:

1.  If Fiscal period = 1 then ending balance = beginning balance + Net change

2.  If fiscal period is > 1 then ending balance = ending balance from previous + netchange

Not sure if this is enough information to make my question clear.  But I am struggling with how to get this done in Mcode.

Thanks for your help

Then when i have the ending balances for all the individuals, I will want to do visuals that can pull it for the month, QTR and year.

Balance Sheet Type	Account Nbr	Fiscal Year	Fiscal Period	Beginning Balance	NetChange	Ending Balance
Assets	1234-00-000	2021	1	$1,000.00	$52.00
Assets	1234-00-000	2021	2		-$96.00
Assets	1234-00-000	2021	3		$500.00
Assets	1234-00-000	2021	4		$750.00
Assets	1234-00-000	2021	5		$350.00
Assets	1234-00-000	2021	6		-$698.00
Assets	1234-00-000	2021	7		$789.00
Assets	1234-00-000	2021	8
Assets	1234-00-000	2021	9
Assets	1234-00-000	2021	10
Assets	1234-00-000	2021	11
Assets	1234-00-000	2021	12
Assets	5678-00-000	2021	1	$555.00	$58.00
Assets	5678-00-000	2021	2		$1,616.00
Assets	5678-00-000	2021	3		-$99.00
Assets	5678-00-000	2021	4		$458.00
Assets	5678-00-000	2021	5		$222.00
Assets	5678-00-000	2021	6		$111.00
Assets	5678-00-000	2021	7		$7,979.00
Assets	5678-00-000	2021	8
Assets	5678-00-000	2021	9
Assets	5678-00-000	2021	10
Assets	5678-00-000	2021	11
Assets	5678-00-000	2021	12
Assets	3456-00-000	2021	1	$987.00	$111.00
Assets	3456-00-000	2021	2		$222.00
Assets	3456-00-000	2021	3		$333.00
Assets	3456-00-000	2021	4		$444.00
Assets	3456-00-000	2021	5		$555.00
Assets	3456-00-000	2021	6		$666.00
Assets	3456-00-000	2021	7		$777.00
Assets	3456-00-000	2021	8
Assets	3456-00-000	2021	9
Assets	3456-00-000	2021	10
Assets	3456-00-000	2021	11
Assets	3456-00-000	2021	12

[DataInsights · Accepted solution]

@dcormiernj,

Here's a DAX solution. Calculated column:

Ending Balance = 
VAR vAccount = Table1[Account Nbr]
VAR vYear = Table1[Fiscal Year]
VAR vPeriod = Table1[Fiscal Period]
VAR vBegBal =
    CALCULATE (
        SUM ( Table1[Beginning Balance] ),
        ALLEXCEPT ( Table1, Table1[Account Nbr], Table1[Fiscal Year] ),
        Table1[Fiscal Period] = 1
    )
VAR vTable =
    FILTER (
        Table1,
        Table1[Account Nbr] = vAccount
            && Table1[Fiscal Year] = vYear
            && Table1[Fiscal Period] <= vPeriod
    )
VAR vResult =
    vBegBal + SUMX ( vTable, Table1[NetChange] )
RETURN
    vResult

[dcormiernj]

Thank you for your response.  This is great.  However, I had also found and used the DAYSYTD time intelligence function and that worked very easily.  thank you

[DataInsights · Accepted solution]

@dcormiernj,

When using the DATESYTD function (or any time intelligence function), be sure to use a date table that has a relationship with the fact table. You can create a date column in the fact table and create a relationship using the date column. Here's the measure in case anyone is interested:

Ending Balance = 
CALCULATE (
    SUM ( Table1[Beginning Balance] ) + SUM ( Table1[Net Change] ),
    DATESYTD ( DimDate[Date] )
)

In the visual, Fiscal Year and Fiscal Period should be from the date table.

[DataInsights · Accepted solution]

@dcormiernj,

Here's a DAX solution. Calculated column:

Ending Balance = 
VAR vAccount = Table1[Account Nbr]
VAR vYear = Table1[Fiscal Year]
VAR vPeriod = Table1[Fiscal Period]
VAR vBegBal =
    CALCULATE (
        SUM ( Table1[Beginning Balance] ),
        ALLEXCEPT ( Table1, Table1[Account Nbr], Table1[Fiscal Year] ),
        Table1[Fiscal Period] = 1
    )
VAR vTable =
    FILTER (
        Table1,
        Table1[Account Nbr] = vAccount
            && Table1[Fiscal Year] = vYear
            && Table1[Fiscal Period] <= vPeriod
    )
VAR vResult =
    vBegBal + SUMX ( vTable, Table1[NetChange] )
RETURN
    vResult