Skip to main content
cancel
Showing results for 
Search instead 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

Reply
chayanupadhyay
Helper III
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:

 

PeriodYearQuarterValueYear Rank
Q3 20192019Q311
Q1 20182018Q122
Q2 20192019Q231
Q2 20182018Q252
Q3 20182018Q362
Q4 20182018Q462

 

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.

Thank you in advance 🙂

2 ACCEPTED SOLUTIONS
v-lili6-msft
Community Support
Community Support

hi @chayanupadhyay 

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:

2.JPG

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.

View solution in original post

Thank you @v-lili6-msft  for your reply and solution.

 

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

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi @chayanupadhyay 

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:

2.JPG

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.

Thank you @v-lili6-msft  for your reply and solution.

 

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

parry2k
Super User
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.

Thank you @parry2k  for your reply.

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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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