topic Re: DAX - EARLIER - % change one cell down in DAX Commands and Tips

DAX - EARLIER - % change one cell down

SuperSusa — Fri, 17 Feb 2023 14:23:53 GMT

Hi,

I am new to PowerBi and this is my first time posting here. What a fantastic forum! Thank you all for your time and sharing your knowledge, you really help people out there a lot!

I would like to generate % change from the actual month to the previous months and have therefore created two index columns with Power Query and then used the EARLIER command.

Then I found two wys of calculating % change but the problem is the same, the results should be down by one line.

Here is the second formula:

In the forum it seems like there are possibilities to copy the table and then merge with PowerQuery, but in this case it is the position of the DAX output that is causing me trouble.

Any suggestions are very much appreciated, thank you!

Re: DAX - EARLIER - % change one cell down

tamerj1 — Fri, 17 Feb 2023 15:30:22 GMT

Hi SuperSusa
Just interchange between Index and Index.1 like

FILTER ( 'Table', 'Table'[Index.1] = EARLIER ( 'Table'[Index] ) )

this will retrieve the previous row. Then you can easily calculate the difference then the percentage.

Re: DAX - EARLIER - % change one cell down

SuperSusa — Fri, 17 Feb 2023 20:07:55 GMT

Hi,

Thank you for your reply.

I tried, and it comes close now, but thee values do still not come out right. Since the signs were negative, I converted to positive and vice versa via 0- in front of the equation.

Re: DAX - EARLIER - % change one cell down

tamerj1 — Sat, 18 Feb 2023 00:49:43 GMT

SuperSusa

It should be

( CurrentRowPrice - PreviousRowPrice ) / PreviousRowPrice

Re: DAX - EARLIER - % change one cell down

tamerj1 — Sat, 18 Feb 2023 05:05:58 GMT

SuperSusa
Like this

% Change = VAR CurrentPrice = 'PQ_Bitumen'[Price] VAR PreviousPrice = MAXX ( FILTER ( 'PQ_Bitumen', 'PQ_Bitumen'[Index.1] = EARLIER ( 'PQ_Bitumen'[Index] ) ), 'PQ_Bitumen'[Price] ) RETURN DIVIDE ( CurrentPrice - PreviousPrice, PreviousPrice )

Re: DAX - EARLIER - % change one cell down

FreemanZ — Sat, 18 Feb 2023 06:03:08 GMT

hi SuperSusa

try like:

％Change =

VAR _pricepre =

MINX(

FILTER(

TableName,

TableName[Dato] = EDATE(EARLIER(TableName[Dato]), -1)

[Price]

)

RETURN

DIVIDE([Price]-_pricepre, _pricepre)

Re: DAX - EARLIER - % change one cell down

SuperSusa — Mon, 20 Feb 2023 09:36:08 GMT

tamerj1 this worked perfectly, thank you so much , you saved my day or should I better say my whole week!:)