DAX - EARLIER - % change one cell down

[SuperSusa]

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!

[FreemanZ]

hi @SuperSusa 

try like:

％Change =

VAR _pricepre =

MINX(

    FILTER(

        TableName, 

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

    ),

    [Price]

)

RETURN

DIVIDE([Price]-_pricepre, _pricepre)

[tamerj1 · Accepted solution]

@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 )

[SuperSusa]

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

[tamerj1]

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.

[SuperSusa]

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. 

[tamerj1]

@SuperSusa 

It should be

( CurrentRowPrice - PreviousRowPrice ) / PreviousRowPrice