Forum Discussion
Solved
Tracking peak Equity over time
I am working on a dashboard where I need to track the max value from the start of the data to now. The dataset has multiple strategies in it denoted by "StratID" (1000000, 2000000, 3000000, etc...
When I have a single strategy the formula below works fine, however, once I have multiple strategies I can't think of a way to make sure the Lookback period only goes to trade 1 of the current set.
It's like I need to group via the stratID apply the Max calc and then expand it back out.
I can do this via a measure as filters will separate out the values, but I really want it in the columns for other analysis I need to do down the line.
Any help would be great.
C_Peak Strat =
CALCULATE (
max ( [Balance] ),
all(History),
History[Trade ID] <= EARLIER ( History[Trade ID])
)
A sample of the data is below. Thanks
| StratID | Trade ID | Balance |
| 1000000 | 1 | 10000 |
| 1000000 | 2 | 10010 |
| 1000000 | 3 | 10100 |
| 1000000 | 4 | 10090 |
| 1000000 | 5 | 10300 |
| 1000000 | 6 | 10290 |
| 2000000 | 1 | 20000 |
| 2000000 | 2 | 20100 |
| 2000000 | 3 | 20150 |
| 2000000 | 4 | 20099 |
| 2000000 | 5 | 20095 |
| 2000000 | 6 | 20145 |
| 2000000 | 7 | 20300 |
| 2000000 | 8 | 20250 |
You can use ALLEXCEPT,
C_Peak Strat = CALCULATE ( MAX ( [Balance] ), ALLEXCEPT ( History, History[StratID] ), History[Trade ID] <= EARLIER ( History[Trade ID] ) )
