Calculate % difference between each year of a row

[kadekwijaya]

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!

[FreemanZ]

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)

[kadekwijaya]

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?

[v-zhangti · Accepted solution]

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.