topic Re: Percentage of difference between previous quarters in DAX Commands and Tips

Percentage of difference between previous quarters

Silvia_GB — Sat, 07 Sep 2024 18:31:35 GMT

Hi,

I am trying to create a measure that calculates the percentage of difference between my previous quarters (all in the same year), so basically Q4 vs Q3, Q3 vs Q2 and Q2 vs Q1. I created the following measure but it only gives me 0. I created the different variables separatley and also changing the quarters type of data, cause I have it as text, like Q1 2025, Q2 2025, Q3 2025, and Q4 2025.

What I am also not sure if I did right is about using the Q Patients measure, which is a measure that takes the current patients from the Product and Quarter selected on the filter in the page

PercentageChange = VAR SelectedQuarter = SELECTEDVALUE('Total Patients Quarters'[Quarters]) VAR SelectedProduct = SELECTEDVALUE('FY 2025'[Product]) VAR CurrentQtr = VALUE(MID(SelectedQuarter, 2, 1)) VAR PreviousQtr = IF(CurrentQtr = 1, 4, CurrentQtr - 1) VAR PQ = "Q" & PreviousQtr VAR CurrentPatients = [Q Patients] VAR PreviousPatients = CALCULATE( [Q Patients], 'FY 2025'[Product] = SelectedProduct, 'Total Patients Quarters'[Quarters] = PQ ) RETURN IF( ISBLANK(PreviousPatients), BLANK(), DIVIDE(CurrentPatients - PreviousPatients, PreviousPatients, 0) )

Hope someone can help me out

Thanks,

Silvia

Re: Percentage of difference between previous quarters

lbendlin — Sat, 07 Sep 2024 20:29:42 GMT

Make it a habit to include a calendar table in your data model, and to base all time related calculations on that calendar table.

Re: Percentage of difference between previous quarters

sanalytics — Sun, 08 Sep 2024 08:59:46 GMT

Silvia_GB
As lbendlin mentioned it is always to have proper schema (Preferebly Star schema with no bidrectional relationship)) or Data model. DAX work best in the schema.
Now coming to your solution, i have considered two tables DimDate and FactSales.

I have created 3 measures in order to achieve your solution. We can reduce the measures number but just created for the sake of your understanding.

Step 1 : Your DateDimension should have Quarter Sort column and Based on that create following measure.

VAR _Calc = CALCULATE( [Sales] , FILTER( ALL( DimDate[QuarterSort],DimDate[Quarter & Year], DimDate[Year] ), DimDate[QuarterSort] = MAX(DimDate[QuarterSort] )-1 && DimDate[Year] = MAX( DimDate[Year] ) ) ) VAR _Result = IF( ISBLANK( _Calc ), 0, _Calc ) RETURN _Result

Step 2 : Create a measure which will show the correct total.

SUMX( VALUES( DimDate[Quarter & Year] ), [PrevQCalculation] )

Step 3 : Then Created final QoQ% Measure.

VAR _CurrentSales = [Sales] VAR _PriorQuarter = [Prior Quarter] VAR _Result = DIVIDE( _CurrentSales - _PriorQuarter,_PriorQuarter,0 ) RETURN _Result

Below is the screenshot

Below is the attached file

https://we.tl/t-gZ8bxJyFYW

Hope, it helps

Regards,

sanalytics

If it is your solution then please like and accept it as solution

Re: Percentage of difference between previous quarters

FlipFlop1 — Sun, 08 Sep 2024 14:30:22 GMT

If you have a date table, you may be able to use the PREVIOUSQUARTER() function

Prev Qtr Sales =

VAR SalesPrevQtr = CALCULATE(SUMX(FactOnlineSales, FactOnlineSales[SalesAmount]), PREVIOUSQUARTER(DimDate[Datekey]))

RETURN

SalesPrevQtr

Re: Percentage of difference between previous quarters

Silvia_GB — Sun, 08 Sep 2024 17:29:08 GMT

Hi sanalytics thanks for your answer, I actually tried to figure out what was wrong with my previous code and for that I broke it down, in 3 single measures (as you did) and realize that the Current Patients for the selected quarter and the Current Patients from my previous quarter were being well calculated.

The problem comes with the % of Difference between the two measures, please see below picture (KPIs cards for each country) where it displays the current and previous quarter patients and the % Diff, that as you can see based for the values displayed is being miscalculated (I dont know why, I used your formula (measure 3)).

Also answering to lbendlin I dont see the benefit of creating a calendar table cause at the end Power BI will take it as text, since there is no format (at least that I am aware of) to treat the Quarters data as a date

Thanks,

Silvia

Re: Percentage of difference between previous quarters

lbendlin — Sun, 08 Sep 2024 19:51:31 GMT

Add a fake date column to your fact table, for example the first day of the quarter.

Re: Percentage of difference between previous quarters

Anonymous — Tue, 24 Sep 2024 02:57:34 GMT

Hi Silvia_GB ，
It's been a while since I heard back from you and I wanted to follow up. Has the problem you were experiencing been resolved? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Re: Percentage of difference between previous quarters

Silvia_GB — Thu, 07 Nov 2024 10:28:22 GMT

Hi, sorry for my late answer. Yes, the problem was solved but I dont know how to mark the the post as resolved, that's why I didnt do it.