Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamically indexing time series

Hi all,

 

I'm trying to dynamically calculate indexed time series (Indexed return). I'm filtering data with various slicers and below you can  see result of it. I'm also using slicer to change time period. In this case first row in Indexed return is always 100 and following values below it should change based on daily_change.

 

This works fine when calculated column is used as a daily change but what goes with measure used in this case?

 

 

To calculate daily_change, I'm using measure 

daily_change = IFERROR([daily value]/[previous daily value]-1,0)

 

To calculate Indexed return, following measure is used

Indexed return =
VAR MinDate = CALCULATE(MIN(Sheet1[Date]), ALLSELECTED(Sheet1[Date]))
RETURN
IF(
MIN(Sheet1[Date]) = MinDate,
100,
CALCULATE(
PRODUCTX(Sheet1,(1+Sheet1[daily_change]))
*100,
FILTER (
ALL ('Sheet1'),
Sheet1[Date] > MinDate
&& Sheet1[Date] <= MIN(Sheet1[Date])
)
)
)

 

 

Thx in advance.

Cheers,

 

Juha

 

 

 

 
 
 
Need Help

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    sorry I didn't understand the problem.

    How should the result look like?

     

    Thank you and best regards

    Denis

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi selimovd ,

       

      Thx for your reply.

       

      Idea is to multply previous day's value by (1+todays daily_change) ie for example 100 * (1 + 0.01) = 101.

       

       

      hope this helps you to help me.

       

      BR,

      Juha

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi selimovd ,

     

    I managed to isolate problem to to "daily_change" in

     

    Indexed return =
    VAR MinDate = CALCULATE(MIN(Sheet1[Date]), ALLSELECTED(Sheet1[Date]))
    RETURN
    IF(
    MIN(Sheet1[Date]) = MinDate,
    100,
    CALCULATE(
    PRODUCTX(Sheet1,(1+Sheet1[daily_change]))
    *100,
    FILTER (
    ALL ('Sheet1'),
    Sheet1[Date] > MinDate
    && Sheet1[Date] <= MIN(Sheet1[Date])
    )
    )
    )

     

    If daily_change is a column, calculation works fine. If daily_change is a measure, it doesn't work. 

     

    Hope it explains the problem bit better.

     

    BR, Juha