topic Re: Calculate % from previous Quarters with ability to use filter in DAX Commands and Tips

Calculate % from previous Quarters with ability to use filter

lucie_rabochova — Wed, 25 Oct 2023 10:21:18 GMT

Hi all,

Hope somebody can help me.

I have simple table (viz print screen) contains Quarters, Number, % from previous period.

BUT I want to use Quarter filter and compare for example 2021-Q1 and 2022-Q1, this is problem, because it doesnt work anymore, viz printscreen.

As you can see, it is still calculating previous period and it is not able to be dynamic.

This is my formula:

QoQ (%) =

VAR V = CALCULATE([Views],PREVIOUSQUARTER(DimDates[Date]))

RETURN

DIVIDE([Views]-V,V)

How can I change the above formula to cooperate with filter and be dynamic?

Thanks a lot for your help

Walter_W2022 — Wed, 25 Oct 2023 14:14:55 GMT

Please use OFFSET and ALLSELECTED in dax to calculate previous row value, please see below and attched PBI file

_Prev_Quarter =

CALCULATE(

[_Sales_Amount],

OFFSET(

-1,

ALLSELECTED('Table'[YearQuarter]),

ORDERBY('Table'[YearQuarter],ASC),

DEFAULT

)

Walter_W2022 — Wed, 25 Oct 2023 14:19:59 GMT

20231026-example.pbix
Here is the exaple file.

lucie_rabochova — Wed, 25 Oct 2023 16:14:37 GMT

Thank you so much! This is amazing solution and it works perfect.

All the best, Lucie