Calculate opening and closing balances across multiple periods

[hgrangeret]

Hi,

Here is my dataset:

Account	PERIOD YEAR	PERIOD NAME	Beginning Balance	Closing Balance
10040	2019	Apr-19	960019	775211
10040	2019	Aug-19	219255	219255
10040	2019	Jul-19	214425	219255
10040	2019	Jun-19	785203	214425
10040	2019	May-19	775211	785203
10040	2019	Oct-19	219255	219255
10040	2019	Sep-19	219255	219255

I have opening and closing balances for different Gl accounts  for a given fiscal year, for different periods.

My users want to filter on a set of periods (in the same FY), eg from Apr-19 to May-19 and show the opening and closing balances  for each account of the selected range of periods.

It should look like this:

Account	PERIOD YEAR	PERIOD NAME	Beginning Balance	Closing Balance	Beginning Balance of Selected Periods	Closing Balance of Selected Periods
10040	2019	Apr-19	960019	775211	960019	214425
10040	2019	May-19	775211	785203	960019	214425
10040	2019	Jun-19	785203	214425	960019	214425

How can I obtain these measures Beginning Balance of Selected Periods and Closing Balance of Selected Periods?

Thanks.

[parry2k · Accepted solution]

@hgrangeret try these two measures

Start Balance = 
VAR minDate = CALCULATE( MIN( Table2[PERIOD NAME] ), ALLSELECTED( Table2[PERIOD NAME] ) )
RETURN CALCULATE( FIRSTNONBLANK( Table2[Beginning Balance], 1), FILTER( ALLEXCEPT( Table2, Table2[Account] ), Table2[PERIOD NAME] = minDate ) )

End Balance = 
VAR maxDate = CALCULATE( MAX( Table2[PERIOD NAME] ), ALLSELECTED( Table2[PERIOD NAME] ) )
RETURN CALCULATE( LASTNONBLANK( Table2[Closing Balance], 1), FILTER( ALLEXCEPT( Table2, Table2[Account] ), Table2[PERIOD NAME] = maxDate ) )

[hgrangeret]

Thanks parry2k,

It did work as expected.