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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
n5722
Helper I
Helper I

Current vs Previous Quarter in a transition year

Hello,

I made a custom measure that compares the current quarter to the previous quarter based on my selection on the bar chart. Meaning if I select Q2 2021 it will calculate the difference % vs Q1 2021.

The problem that I am facing now is that when I select Q1 2021 it does not compare it to Q4 2020 (see pic below).

Screenshot 2021-09-10 at 11.37.00.png

 

Here is the DAX code of my custom measure:

Screenshot 2021-09-10 at 11.38.17.png

 

Here is the plane code:

TotalSalesDiff% (selected vs prev quarter) = 
VAR SelectedQ = 
    SELECTEDVALUE('Date helper (create date)'[Date].[QuarterNo])
VAR SelectedYear = SELECTEDVALUE('Date helper (create date)'[Year])
VAR TotalSalesSelectedQ = 
    CALCULATE([TotalSales], FILTER(ALL('Date helper (create date)'), 'Date helper (create date)'[Year] = SelectedYear
     && 'Date helper (create date)'[Date].[QuarterNo] = SelectedQ))
VAR TotalSalesPrevQ = 
    CALCULATE([TotalSales], FILTER(ALL('Date helper (create date)'), 'Date helper (create date)'[Year] = SelectedYear
     && 'Date helper (create date)'[Date].[QuarterNo] = SelectedQ - 1))

RETURN
DIVIDE((TotalSalesSelectedQ-TotalSalesPrevQ),TotalSalesSelectedQ,0)+0

 

Side note: I use a date helper table that calculates relative time (weeks, months, years etc.)

Could someone tell me how to improve my measure so that I can get the expected result? Thanks!

2 REPLIES 2
amitchandak
Super User
Super User

@n5722 , if you have dates, You can use time intelligence

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

if you do not have , You need to a separate table for Qtr Year (a date table at the higher level  )

 

Create a rank column on Qtr start date or Year Qtr YYYYQ

 

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense)

 

then create measures like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak . Thank you for your reply but this does not fix my problem. I am not trying to calculate QTD sales, I am trying to calculate the difference between the currently selected month and the month before it.

My measure works great if the calculation is taking place within the year but when I select Q1 2021 it does not calculate the difference vs Q4 of 2020 as it stays in the context of 2021.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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