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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
yachoi
Frequent Visitor

Percent Change of Calculation from Last Quarter with Quarterly Calculation Grouping

I am trying to figure out how to best calculate the percent change of a calculation from last quarter to this quarter. I have a table like below where there are multiple rows for the fiscal quarters. 

 

fiscal_quarterfiscal_weeksubsInclude
FY23Q1116Y
FY23Q12124Y
FY23Q1315N
FY22Q44510Y
FY22Q446203Y
FY22Q44712N
FY22Q44860N

 

I have a separate calculation DI_percent where it is filtered based on another column (e.g. Include). How can I compare the DI_percent of the previous quarter? 

At first I tried rankx, but that seemed to have given a value to each row which wouldn't compare. My columns do not have actual dates and is completely based on fiscal year (not calendar year) so I couldn't use dateadd. Is there some other function that I am missing? 

 

 

Thank you!

2 REPLIES 2
Sahir_Maharaj
Super User
Super User

Hello @yachoi,

 

Use the LAG function in Power BI to compare the DI_percent of the previous quarter. The LAG function returns the value of a column from a previous row in the table, based on an offset that you specify.

 

In your case, you can use an offset of -1 to get the value of the DI_percent for the previous quarter:

 

DI_percent_change = 
VAR current_quarter = SELECTEDVALUE('Table'[fiscal_quarter])
VAR current_DI_percent = CALCULATE(SUM('Table'[DI_percent]), 'Table'[fiscal_quarter] = current_quarter)
VAR previous_DI_percent = CALCULATE(SUM('Table'[DI_percent]), 'Table'[fiscal_quarter] = LAG(current_quarter, 1))
RETURN 
    IF(ISBLANK(previous_DI_percent), BLANK(), (current_DI_percent / previous_DI_percent) - 1)

 

The LAG function requires that the table has an order, so you may need to sort the table by the fiscal_quarter column before using this measure.

 

Hope this helps.

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Thanks for your response. I am getting an error that LAG is not the correct syntax? Looking up more on LAG it seems like it would go back 1 row? Does the var current_quarter aggregate the quarters? 

Also the DI_percent is already a calculated field so I can't sum it. Not sure if that might be causing the issues (like below)?

 

DI_percent_change = 
VAR current_quarter = SELECTEDVALUE('Table'[fiscal_quarter])
VAR current_DI_percent = CALCULATE('Table'[DI_percent], 'Table'[fiscal_quarter] = current_quarter)
VAR previous_DI_percent = CALCULATE('Table'[DI_percent], 'Table'[fiscal_quarter] = LAG(current_quarter, 1))
RETURN 
    IF(ISBLANK(previous_DI_percent), BLANK(), (current_DI_percent / previous_DI_percent) - 1)

 Thank you. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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