write a measure for alternating opening balances and closing balances Year on year

[feltaha]

Hi, 

Is there a way to create a Measuer for the opening balance that will change based on the selected year? the opening balance for the year 2019 is zero, while the closing balance for 2019 = OB2019 (Opening Balance for 2019) + Debit column - Credit Column. for the year 2020 opening balance = Closing Balance for 2019, and closing balance for 2020 = OB2020 + Debit column - Credit Column and so on and so forth. 

Below are the formulas that I have created, but I am sure there is a better way of doing this rather than the manual way. 

//separate measure for the opening balances
Actuals Opening Selected Period = 

var selectedperiod = SELECTEDVALUE(Date[Year])

Return 

switch
    (
    true(),
    selectedperiod=2019, OB2019,
    selectedperiod=2020, [Closing 2019],
    selectedperiod=2021, [Closing 2020],
    selectedperiod=2022, [Closing 2021],
    selectedperiod=2023, [Closing 2022]
)
//separate measure for the closing balances
Actuals Closing Selected Period = 

var selectedperiod = SELECTEDVALUE(Date[Year])

Return 

switch
    (
    true(),
    selectedperiod=2019, [Closing 2019],
    selectedperiod=2020, [Closing 2020],
    selectedperiod=2021, [Closing 2021],
    selectedperiod=2022, [Closing 2022],    
    selectedperiod=2023, [Closing 2023]   )

OB2019 = CALCULATE(([Credit]), Actuals[ACCDATE] = 2018) //which equals to zero because there are no transactions as of 2018. 

Closing 2019 = OB2019 + [Debit 2019] - [Credit 2019]

Closing 2020 = [Closing 2019] + [Debit 2020] - [Credit 2020]

Closing 2021 = [Closing 2020] + [Debit 2021] - [Credit 2021]

Closing 2022 = [Closing 2021] + [Debit 2022] - [Credit 2022]
//and so on and so forth.

These opening balance and closing balance measures will go in the below visual for each of the companies. Plus they will change based on the "Year" slicer at the top. 

With my approach, I have to create a measure for 2019, 2020, 2021, etc. I am looking for a way to automate this. 

[amitchandak · Accepted solution]

@feltaha , we have build that using running total approch

closing = CALCULATE(SUM(Table[debit]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[credit]),filter(date,date[date] <=maxx(date,date[date])))

opening   = CALCULATE(SUM(Table[debit]),filter(date,date[date] <min(date,date[date]))) - CALCULATE(SUM(Table[credit]),filter(date,date[date] < min(date,date[date])))

very similar to Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[feltaha]

Hi @amitchandak , 

Thank you for your guidance, for I was able to solve the issue using your suggested running total method. 
I got rid of the error by changing the filter expression of min (date, date[date]), but I was getting empty values. then I altered the formula a little bit using keepfilters instead of filter and now it works.

below is the code I used. 

//Opening balance code. 
OB2 = CALCULATE(
    [Rand Debit],
    KEEPFILTERS('Date'[Date] < MIN('Date'[Date]))
    )
     - 
     CALCULATE(
    [Rand Credit],
    KEEPFILTERS('Date'[Date] < min('Date'[Date]))
)

//Rand debit Code
Rand Debit = 
CALCULATE (
    SUMX (
        Actuals,
        IF (
            Actuals[Company] = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = 1
)

//Rand Credit Code
Rand Credit = 
CALCULATE (
    SUMX (
        Actuals,
        IF (
            Actuals[Company] = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = -1
)

//Closing balance code
Rand CB = [OB2] + [Rand Debit] -[Rand Credit]

[feltaha]

Hi Amit, 

I am getting the following error when using your formula. The error I get is "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

Below is the formula I used. 

Rand OB = 
CALCULATE(
	SUMX (
        Actuals,
        IF (
            Actuals[Company] = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = 1,
FILTER('Date','Date'[Date] < MIN('Date', 'Date'[Date]))
) 

- 

CALCULATE(
SUMX (
        Actuals,
        IF (
            SELECTEDVALUE ( Actuals[Company] ) = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = -1,
FILTER('Date','Date'[Date] < MIN('Date', 'Date'[Date]))
)

 For the closing balance, there is no error, however, I suspect that the calculation is a bit off. But will be able to better investigate after fixing the opening balance. Below is the formula for the closing balance: 

Rand CB = CALCULATE(SUMX (
        Actuals,
        IF (
            Actuals[Company] = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = 1,
FILTER('Date','Date'[Date] <= MAXX('Date','Date'[Date]))
) 
- 
CALCULATE( SUMX (
        Actuals,
        IF (
            SELECTEDVALUE ( Actuals[Company] ) = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = -1,
FILTER('Date','Date'[Date] <= MAXX('Date','Date'[Date]))
)

[feltaha]

I thought I'll put some data that might help:

My data model:

Sample data of the actuals table:

Account	ACCDATE	Company	Sign	Amount
11501001	1/9/2019	U07	-1	23.43
11501001	1/10/2019	U09	-1	7.41
11501001	1/11/2019	U10	-1	73.28
11501001	1/12/2019	U12	-1	35.95
11501001	1/13/2019	U21	1	14.18
11501003	1/14/2019	U09	1	75.40
11501003	1/15/2019	U15	1	45.31
11501003	1/16/2019	Z02	1	43.36
11501003	1/17/2019	U08	1	23.87
11501004	1/18/2019	U09	1	62.80
11501004	1/19/2019	U15	1	45.51
11501004	1/20/2019	U08	1	53.53
11501005	1/21/2019	U09	-1	5.58
11501005	1/22/2019	U13	-1	4.84
11501005	1/23/2019	U14	-1	39.65
11501005	1/24/2019	U15	-1	99.27
11501005	1/25/2019	Z02	-1	21.32
11501005	1/26/2019	U13	-1	52.67
11501006	1/27/2019	U08	-1	79.46
11501006	1/28/2019	U21	1	61.56
11501007	1/29/2019	U11	1	84.15
11501007	1/30/2019	U11	1	65.63
11501009	1/31/2019	U21	1	13.85
11501010	2/1/2019	U21	1	34.94
11501011	2/2/2019	Z02	1	8.28
11501012	2/3/2019	U09	1	20.94
11501012	2/4/2019	U21	1	58.95
11501013	2/5/2019	Z02	1	18.27
11501013	2/6/2019	U08	1	93.30
11501013	2/7/2019	U13	1	33.46
11501014	2/8/2019	U15	1	2.53
11501014	2/9/2019	U21	1	3.34
11501014	2/10/2019	U08	1	34.80
11501014	2/11/2019	U08	1	2.41
11501015	2/12/2019	Z02	1	14.00
11501016	2/13/2019	U21	1	84.98
11501016	2/14/2019	U15	1	18.68
11501017	2/15/2019	U09	1	37.80
11501018	2/16/2019	U15	1	357.72
11501018	2/17/2019	U21	1	336.85
11501019	2/18/2019	U13	1	657.19
11501019	2/19/2019	U08	1	544.88
11501019	2/20/2019	U09	1	923.10
11501020	2/21/2019	U13	1	930.18
11501021	2/22/2019	U15	1	400.58
11501021	2/23/2019	U21	1	762.21

Sample data of the 'Date' table with exchange rates:

Date	ZAR
1/1/2019	3.9
1/2/2019	3.6
1/3/2019	4.1
1/4/2019	4.2
1/5/2019	4.3
1/6/2019	4.4
1/7/2019	4.5
1/8/2019	4.6
1/9/2019	4.7
1/10/2019	4.8
1/11/2019	4.9
1/12/2019	4.2
1/13/2019	4.6
1/14/2019	4.7
1/15/2019	4.1
1/1/2020	4.2
1/2/2020	4.3
1/3/2020	4.2

Opening balance measure: I changed the formula a bit to get rid of the above "scalar value" error, but now the visual is returning empty cells. 

Rand Opening Balance = 
CALCULATE(
[Rand Debit],
FILTER('Date','Date'[Date] < MIN('Date'[Date]))
) 

- 

CALCULATE(
[Rand Credit],
FILTER('Date','Date'[Date] < MIN('Date'[Date]))
)

Rand Debit Dax formula:

Rand Debit = 
CALCULATE (
    SUMX (
        Actuals,
        IF (
            Actuals[Company] = "Z02",
            DIVIDE ( Actuals[Amount], RELATED ( 'Date'[ZAR] ) ),
            Actuals[Amount]
        )
    ),
    Actuals[Sign] = 1
)

The outcome matrix visual: