Forum Discussion

andrewkla's avatar
andrewkla
Frequent Visitor
3 years ago

Table variable to calculate stuff based on slicer selection

I have a table called FE with multiple security prices (called Price) for each Day over around 3 years.

The daily prices are expressed in USD, so some start at $10 and some start at $1000.

What I'm trying to build is a measure, which effectively takes a sub-section of this big table and stores the small table, with values between dates selected on the Date slicer.

I then want to use this for a visual but I want it so that whatever is selected on the Date slicer, the visual always starts at zero, so the performance is relative. 

I have managed to turn the daily unit price into daily change, then daily change in % terms and then I have another column with Cumulative Return, which does what I want, if I show the entire date range on my visual. But it does not work with the slicers. For reference this is my 

Cumulative Return =
VAR MinDate = CALCULATE(MIN(FE[Date]), ALL(FE))
RETURN
IF(FE[Date] = MinDate, 1, PRODUCTX(FILTER(FE, FE[Citi Code] = EARLIER(FE[Citi Code]) && FE[Date] <= EARLIER(FE[Date])),1 + DIVIDE(FE[Daily Change %], 100)))
 
So I'm trying to recreate the above but in a measure that reacts to the slicers. So far I got:
 
 
Performance1 =
VAR MinSelectedDate = MIN('Date'[Date])
VAR MaxSelectedDate = MAX('Date'[Date])
VAR MinSelectedValue = 1
VAR PerformanceTable =
ADDCOLUMNS(
FILTER('FE',
'FE'[Date] >= MinSelectedDate
&& 'FE'[Date] <= MaxSelectedDate),
"Date2", 'FE'[Date],
"Performance", 'FE'[??? not sure ???]
)
RETURN

???

 

 

Need Help