Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stanislav_dugas
Helper III
Helper III

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

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.

 

stanislav_dugas_0-1624105404321.png

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.

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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
)
)
)
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
stanislav_dugas
Helper III
Helper III

@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
Helper III
Helper III

Thanks alot @Jihwan_Kim .

 

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

Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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
)
)
)
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


ryan_mayu
Super User
Super User

@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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.