Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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).
Here is the DAX code of my custom measure:
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!
@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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
134 | |
113 | |
100 | |
68 | |
67 |