cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Same Period Last year comparison on the basis of quarter

Hello,

I have scenario where i need to subtract latest year data (for all quarter) from previous year data (with the same quarter), PFB the sample data:

 Period Year Quarter Value Year Rank Q3 2019 2019 Q3 1 1 Q1 2018 2018 Q1 2 2 Q2 2019 2019 Q2 3 1 Q2 2018 2018 Q2 5 2 Q3 2018 2018 Q3 6 2 Q4 2018 2018 Q4 6 2

I need to create a measure to do following:

Calculate(SUM(Value),Filter(Table, Year Rank = 1)) - Calculate(SUM(Value), Filter(Table, Year Rank = 2))

I have 2019 and 2018 data and i need to perform below steps:

1.   Step A = Sum of 2019 data for all quarters

2.   Step B = Sum of 2018 data for whatever quarter present in 2019

3.    Step C = Step A - Step B

As per above data:

I have created rank for year

2019 = Year Rank 1

2018 = Year Rank 2

Step A:

I have two quarter data for year 2019 (Q2 2019 and Q3 2019)

SUM(Value) Where Year Rank = 1

Step B:

Now i need to sum of values for 2018 data but i need to restrict to only two quarters (Q2 2018 and Q3 2018)

I am having trouble writing the step B.

Appreciate your help in writing above measure logic.

Please let me know if you need more clarity on above scenario.

2 ACCEPTED SOLUTIONS
Community Support

You could try this formula:

Result =

var _table=SUMMARIZE('Table','Table'[Period],'Table'[Year],'Table'[Quarter],"_value",CALCULATE(SUM('Table'[Value]))-CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[Quarter]=MAX('Table'[Quarter]))))
return
SUMX(_table,[_value])

or

Result 2 =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Period],
'Table'[Year],
'Table'[Quarter],
"_value", IF (
ISBLANK ( CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) )),
BLANK (),
CALCULATE ( SUM ( 'Table'[Value] ) )
- CALCULATE (SUM ( 'Table'[Value] ),FILTER (ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) ) )
)
RETURN
SUMX ( _table, [_value] )

Result:

and here is sample pbix file, please try it.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

I tried this approach and it worked for me and i found different solution as well.

I created a flag (custom column) to capture the quarter present for latest period and used that flag as filter while calculating the step 2 (calcualting the sum of values for previous year for same quarters present in latest year)

Thanks again for your help and time.

Regards,

Chayan

4 REPLIES 4
Community Support

You could try this formula:

Result =

var _table=SUMMARIZE('Table','Table'[Period],'Table'[Year],'Table'[Quarter],"_value",CALCULATE(SUM('Table'[Value]))-CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[Quarter]=MAX('Table'[Quarter]))))
return
SUMX(_table,[_value])

or

Result 2 =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Period],
'Table'[Year],
'Table'[Quarter],
"_value", IF (
ISBLANK ( CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) )),
BLANK (),
CALCULATE ( SUM ( 'Table'[Value] ) )
- CALCULATE (SUM ( 'Table'[Value] ),FILTER (ALL ( 'Table' ),'Table'[Year] = MAX ( 'Table'[Year] ) - 1 && 'Table'[Quarter] = MAX ( 'Table'[Quarter] ) ) ) )
)
RETURN
SUMX ( _table, [_value] )

Result:

and here is sample pbix file, please try it.

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

I tried this approach and it worked for me and i found different solution as well.

I created a flag (custom column) to capture the quarter present for latest period and used that flag as filter while calculating the step 2 (calcualting the sum of values for previous year for same quarters present in latest year)

Thanks again for your help and time.

Regards,

Chayan

Super User

@chayanupadhyay you should take advantage of time intelligence DAX functions. There are tons of functions available for compare period over period, here is link to time intelligence functions.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper III

I didn't have any unique date column for using the time intelligence function.

This is kind of extract of actual data which doesn't have all the information in terms of date.

Thanks for the reply, i am able to get through the problem by using the flag and putting the flag as filter.

Regards,

Chayan

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors