Percentage of difference between previous quarters

[Silvia_GB]

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

[Anonymous]

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!

[Silvia_GB · Accepted solution]

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.

[FlipFlop1]

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

[sanalytics]

@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

[Silvia_GB]

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

[lbendlin]

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

[lbendlin]

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