How to calculate percentage change between two dates?

[DrEvil]

Hello, 

I need to calculate the % change in a value between two days in a table. I've tried using the answer provided in another post but i didn't get what i needed. (Title of the other post: How to calculate percentage change from prior date (prior record)?).

 The Measure im using:

Measure = 
VAR previousDateWithPrice =
    CALCULATE (
        MAX ( Query1[Date]);
        FILTER ( ALL ( Query1 ); Query1[ExchangeRate] > 0 && Query1[Date] < MAX ( Query1[Date] ) )
    )
VAR previousPrice =
    CALCULATE (
        SUM ( Query1[ExchangeRate] );
        FILTER ( ALL ( Query1 ); Query1[Date] = previousDateWithPrice )
    )
RETURN
    DIVIDE ( Sum(Query1[ExchangeRate]) - previousPrice; previousPrice; 0)

Some Screenshots:

• Data format
• What i get with the current measure I'm using
• What results i should get

Data format

Table and Results i get

Desired Results

Thanks, 

Edit: Replaced not working link from the other post with it's title.

[CodeCracker]

Worked absolutely fine for me.
I didn't need the ALLEXCEPT filtering for my problem, it just complicated things. Hence, would recommend to start with this solution, implement it and see if it already delivers the desired result.

[Sean · Accepted solution]

@DrEvil

Try something like this...

You need to find the current and previous price for each currency pair

Measure =
VAR currentPrice =
    CALCULATE (
        SUM ( Query1[ExchangeRate] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[ExchangeRate] > 0
                && Query1[Date] = MAX ( Query1[Date] )
        )
    )
VAR previousDateWithPrice =
    CALCULATE (
        MAX ( Query1[Date] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[ExchangeRate] > 0
                && Query1[Date] < MAX ( Query1[Date] )
        )
    )
VAR previousPrice =
    CALCULATE (
        SUM ( Query1[ExchangeRate] );
        FILTER (
            ALLEXCEPT ( Query1; Query1[Currencies] );
            Query1[Date] = previousDateWithPrice
        )
    )
RETURN
    DIVIDE ( currentPrice - previousPrice; previousPrice; 0 )

Hope this helps!

Good Luck!

[Mstrande]

Hi,
I really like this function, very good. But a question, is it possibly to make the percentage calculation scalably. So if I group the currencies (for example in EU, North America, Africa) can i the get Power bi to give the percentage by these groups?

MSTRANDE

[DrEvil]

@Sean 

Thanks a lot for your help, works perfectly!