Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Thanks,
Edit: Replaced not working link from the other post with it's title.
Solved! Go to Solution.
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!
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |