Last years quarter end value to be set as opening value for whole next year

[stanislav_dugas]

I am having bellow data set.

What i need to do is for every year i need to set the last quarter value from previous year as opening value for whole next year.

So for whole 2017 year i would have Opening SE as 

Q1 2017 = 1 969 015 870

Q2 2017 = 1 969 015 870

Q3 2017 = 1 969 015 870

Q4 2017 = 1 969 015 870

I am strugling with composing the right function. 

any help with this is appreciated.

[ryan_mayu · Accepted solution]

@stanislav_dugas 

is this what you want?

Column = MAXX(FILTER('Table (3)',year('Table (3)'[Date])=year(EARLIER('Table (3)'[Date]))-1&&QUARTER('Table (3)'[Date])=4),'Table (3)'[Closing SE])

[stanislav_dugas]

@Jihwan_Kim 

Quick question. How would you adjust the function you sent if you have larger data set where you would have another column splitting each year into various segments so that in visual you can filter for individual segment and the logic would still work. 

ATM you function uses the filter part which eliminates all the slicers in the visuals.

[stanislav_dugas]

Thanks alot @Jihwan_Kim .

Been strugling with it for a while 🙂 your solution worked like a charm.

[Jihwan_Kim · Accepted solution]

Hi, @stanislav_dugas 

Please check the below picture and the sample pbix file's link down below.

I tried to create a sample pbix file based on the explanation.

It is for creating a measure.

Closing SE : =

SUM ( Data[Closing SE] )

Opening SE : =

VAR currentyear =

MAX ( Dates[Year] )

VAR referencequarter = 4

RETURN

IF (

HASONEVALUE ( Dates[Q & Y] ),

CALCULATE (

[Closing SE :],

FILTER (

ALL ( Dates ),

Dates[Year] = currentyear - 1

&& Dates[Quarter] = referencequarter

)

)

)

https://www.dropbox.com/s/zj1zrozjvsbjap8/stanislav.pbix?dl=0 

[ryan_mayu · Accepted solution]

@stanislav_dugas 

is this what you want?

Column = MAXX(FILTER('Table (3)',year('Table (3)'[Date])=year(EARLIER('Table (3)'[Date]))-1&&QUARTER('Table (3)'[Date])=4),'Table (3)'[Closing SE])