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.
Hey all,
I want to calculate the % variation of a stock, but I am having trouble to lock it's earliest and lastest value to perform my calculation.
Stock | MaxDate | year-month | Value |
A | 7/31/2022 | Jul-22 | 14 |
A | 8/31/2022 | Aug-22 | 15 |
A | 9/30/2022 | Sep-22 | 15 |
A | 10/31/2022 | Oct-22 | 12 |
A | 11/30/2022 | Nov-22 | 16 |
B | 7/31/2022 | Jul-22 | 74 |
B | 8/31/2022 | Aug-22 | 65 |
B | 9/30/2022 | Sep-22 | 64 |
B | 10/31/2022 | Oct-22 | 67 |
B | 11/30/2022 | Nov-22 | 80 |
C | 7/31/2022 | Jul-22 | 110 |
C | 8/31/2022 | Aug-22 | 105 |
C | 9/30/2022 | Sep-22 | 90 |
C | 10/31/2022 | Oct-22 | 110 |
C | 11/30/2022 | Nov-22 | 85 |
Besides this table, there is also a Calendar table that is connect by a Date column.
For Stock A I always want to Initial Value to be = 14. Then, as the months go by I want to:
How can I do this? Thanks!
Solved! Go to Solution.
hi @Anonymous
try like:
VAR% =
VAR _price = MAX(TableName[Value])
VAR _stock = MAX(TableName[Stock])
VAR _earliestdate = //earliest date for the current stock
MINX(
FILTER(
ALL(TableName),
TableName[Stock] = _stock
),
TableName[MaxDate]
)
VAR _earliestprice = //value on the earliest date for the current stock
MINX(
FILTER(
ALL(TableName),
TableName[Stock] = _stock
&&TableName[MaxDate]=_earliestdate
),
TableName[Value]
)
RETURN
IF(
_price<>0,
DIVIDE(_price- _earliestprice, _earliestprice)
)
hi @Anonymous
try to plot a table with this:
Hi @FreemanZ !
Thanks, that worked! Just need a small adjustment. How do I get rid of those date that don't have any values?
Hi try like:
@Anonymous
@FreemanZ I had to rephrase the problem.
The solution you present is fine, but it only takes in account that the values from the Value column are always growing. So, I changed the table and what I am looking for is the oldest value for a given Stock and not the minimum.
Cheers!
hi @Anonymous
try like:
VAR% =
VAR _price = MAX(TableName[Value])
VAR _stock = MAX(TableName[Stock])
VAR _earliestdate = //earliest date for the current stock
MINX(
FILTER(
ALL(TableName),
TableName[Stock] = _stock
),
TableName[MaxDate]
)
VAR _earliestprice = //value on the earliest date for the current stock
MINX(
FILTER(
ALL(TableName),
TableName[Stock] = _stock
&&TableName[MaxDate]=_earliestdate
),
TableName[Value]
)
RETURN
IF(
_price<>0,
DIVIDE(_price- _earliestprice, _earliestprice)
)
Since the value of a stock can go to zero, I would just change _price <>0 to NOT(ISBLANK(_price))
this is with less codes and shall also work:
VAR%4 =
VAR _price = MAX(TableName[Value])
VAR _earliestprice =
CALCULATE(
MIN( TableName[Value]),
TOPN(
1,
CALCULATETABLE(TableName, ALLEXCEPT( TableName, TableName[Stock])),
TableName[MaxDate],
ASC
)
)
RETURN
IF(
_price<>0,
DIVIDE(_price- _earliestprice, _earliestprice)
)
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |