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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.