AVERAGE of OPENINGBALANCEYEAR of multiple years

[Mr_Glister]

Hi,

I have a very large dataset that I connect to via Direct Query.

In its simplest form, my problem is this. 

I am using OPENINGBALANCEYEAR to retreive the first value of the year to perform a calculation based on it.

This could be [% of new stores added in period] for example.

If I only look at one year, this works quite well. But when I want to look at multiple years aggregated, I would rather have my calculation use the AVERAGE of the results of OPENINGBALANCEYEAR of the different years.

I'm providing some sample data below.

Let Table1 be my data.

Let the table below be the normal behaviour of OPENINGBALANCEYEAR when the table has years as the filter context in the rows.

The table below is what I'd like to achieve when I remove the Year from the rows. 

(OPENINGBALANCEYEAR would just return 50 and 101)

Can somebody help me with this?

[Mr_Glister]

Thanks @v-stephen-msft  and @amitchandak !

I'm afraid I've made my sample data little more simplistic than the problem actually is.

Would your solutions also work when I in fact use OPENINGBALANCEMONTH? It is really the opening balance of months that I want to average. For example "the average of the opening balances of February" from 2018-2021.

[v-stephen-msft]

Hi @Mr_Glister ,

Sorry for taking so long to reply to you, OPENINGBALANCEMONTH does not seem to work here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-stephen-msft]

Hi @Mr_Glister ,

Here's my solution.

First measure:

Opening Balance =
CALCULATE (
    SUM ( 'Table1'[Number of stores] ),
    FILTER ( 'Table1', MONTH ( [Date] ) = 1 && DAY ( [Date] ) = 1 )
)

Second measure:

Opening Balance 2 =
AVERAGEX ( 'Table1', [Opening Balance] )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Mr_Glister , Try a measure like

calculate(AverageX(Table,calculate( Firstnonblankvalue('Date'[Date], sum(Table[Stores]))) ), filter(allselected('Date'), 'Date'[Year] = max('Date'[Year])))