topic Re: Calculate % difference between each year of a row in DAX Commands and Tips

Calculate % difference between each year of a row

kadekwijaya — Thu, 02 Mar 2023 16:49:25 GMT

Hi,

I am trying to find the % difference e.g. change in consumption between 2021 and 2020 then 2022 and 2021:

This is not a calculated column so can be edited in Power Query editor

Thanks!

Re: Calculate % difference between each year of a row

FreemanZ — Fri, 03 Mar 2023 00:10:30 GMT

hi kadekwijaya

you want a solution in PQ?

With DAX, try plot a visual with Year column and a measure like:

Measure =

VAR _year = MAX (TableName[year])

VAR _ConsLY =

SUMX(

FILTER(

ALL(TableName),

TableName[Year] = _year -1

TableName[consumption]

)

VAR _Cons =

SUM(TableName[consumption])

RETURN

DIVIDE(_Cons - _ConsLY, _ConsLY)

Re: Calculate % difference between each year of a row

kadekwijaya — Fri, 03 Mar 2023 08:29:59 GMT

Thanks for the suggestion!

I would ideally like a table showing the change in consumption as a % between each year?

E.g. 2020 will be blank but 2021 and 2022 will have % values?

Re: Calculate % difference between each year of a row

v-zhangti — Mon, 06 Mar 2023 07:05:53 GMT

Hi, kadekwijaya

You can try the following methods.
Measure:

Change% = Var _Previousyear=CALCULATE(SUM('Table'[Consumption]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])-1)) Var _Currentyear=SUM('Table'[Consumption]) Return IF(_Previousyear=BLANK(),BLANK(),DIVIDE(_Currentyear-_Previousyear,_Currentyear))

Column = Var _Previousyear=CALCULATE(SUM('Table'[Consumption]),FILTER('Table',[Year]=EARLIER('Table'[Year])-1)) Var _Currentyear=[Consumption] Return IF(_Previousyear=BLANK(),BLANK(),DIVIDE(_Currentyear-_Previousyear,_Currentyear))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.