topic Re: growth percentage change quarter over quarter (part 2) in DAX Commands and Tips

growth percentage change quarter over quarter (part 2)

nero692 — Thu, 20 Jul 2023 12:25:03 GMT

Hi everyone,

This is a follow-up from another topic solved here.

Initially, everything worked just fine when I realized my mistake (when you are tired, mistakes can pile up).

My calculation in Excel was not the right one. To get % of growth quarter over quarter, I should have done this:

(Q3 - Q2) / Q2. Initial formula was ok for just one line but not for the rest of the lines. Base quarter should always have been 2022 Q2.

In this case for Q4, it would have been like this (Q4 - Q2) / Q2.

So in the case of value for "bank" field, calculation (with values from "valueadd" field) would be

Example:

for 2022 Q3: (bank value Q3 - bank value Q2) / bank value Q2

for 2022 Q4: (bank value Q4 - bank value Q2) / bank value Q2

for 2023 Q1: (bank value Q1 - bank value Q2) / bank value Q2

for 2023 Q2: (bank value Q2 - bank value Q2) / bank value Q2

And same goes for the "rest of market" field

for 2022 Q3: (rest of market value Q3 - rest of market value Q2) / rest of market value Q2

for 2022 Q4: (rest of market value Q4 -rest of market value Q2) / rest of market value Q2

for 2023 Q1: (rest of market value Q1 - rest of market value Q2) / rest of market value Q2

for 2023 Q2: (rest of market value Q2 - rest of market value Q2) / rest of market value Q2

Values (already calculated in Excel) are i the % growth column for reference.

Initially the code for PowerBi worked but now I don't know where to make changes so it can refer to base value of 2022 Q2.

Quarterly growth =
VAR CurrentQuarter =
    SUM ( 'tbl value'[valueadd] )
VAR PrevQuarter =
    CALCULATE (
        SUM ( 'tbl value'[valueadd] ),
        PREVIOUSQUARTER ( 'Calendar'[Date] )
    )
VAR Result =
    DIVIDE ( CurrentQuarter - PrevQuarter, PrevQuarter )
RETURN
    Result

I thought it would be at this line PREVIOUSQUARTER ('Calendar'[Date]) IN {"2022 Q2"}....but it does not work.

Many thanks in advance.

nero692

Re: growth percentage change quarter over quarter (part 2)

Mkarwa-123 — Thu, 20 Jul 2023 18:16:25 GMT

nero692
You dont need previousquarter() function , you directly can calculate using calulate function
Calculate (SUM(sales[sales]), qaurter ='Q2' & year= currentyear)

Did I answer your question? Mark my post as a solution!

Re: growth percentage change quarter over quarter (part 2)

nero692 — Fri, 21 Jul 2023 02:25:47 GMT

Mkarwa-123

After changing the values to match table values as:

SUM('tbl value'[valueadd], tbl value[quarter] = 'Q2' & tbl value[year] = 2022)

it did not work. At first it said that it cannot find Q2, so I had to replace with "Q2" instead of 'Q2' but still can't make it work. Error message says "DAX comparison do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

Re: growth percentage change quarter over quarter (part 2)

Mkarwa-123 — Fri, 21 Jul 2023 12:01:11 GMT

nero692 You need to use calculate. SUM() function doesn't support filters

Re: growth percentage change quarter over quarter (part 2)

nero692 — Mon, 24 Jul 2023 00:18:01 GMT

Mkarwa-123I used calculate along the sum function and that triggered the error message as explained above. I hsve no idea how else to make it work.