Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
DrEvil
Frequent Visitor

How to calculate percentage change between two dates?

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 formatData format

 

Table and Results i getTable and Results i get

 

Desired ResultsDesired Results

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks, 

 

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

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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! Smiley Happy

View solution in original post

4 REPLIES 4
CodeCracker
New Member

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
Community Champion
Community Champion

@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! Smiley Happy

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
Frequent Visitor

@Sean 

Thanks a lot for your help, works perfectly!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors