Forum Discussion

OCK's avatar
OCK
Frequent Visitor
9 years ago
Solved

indexed time series

Hi All   I'm a complete newbie to DAX, while much more experienced with VBA and MySQL. I'm working with percentage growth time series data, sequentially stored in a table by Identifier, Date and Re...
  • OCK's avatar
    OCK
    9 years ago

    Anybody who's interested, here's the solution after a few hours of try and error:

     

    ReturnTmp =
    CALCULATE (  
               IF ( COUNTROWS ( Table1 ) = 1  ;  100  ;  PRODUCT ( Table1 [Return]  ) * 100  ) ;
               FILTER ( ALL ( Table1 ) ;  Table1[Date] <= MAX (Table1[Date]  )  )  ;
               VALUES ( Table1[Identifier] )
    )

     

    This is a rather standard chart in financial analysis for plotting return series.

OCK's avatar
OCK
Frequent Visitor
9 years ago

Hi Qiuyun Yu,

 

Thank you very much for your reply. Unfortunately this cannot be done with filtering data on the visual, it must be set in the DAX function. The first value in the series must be set to 100 while all following items will apply the formula as outline above. Hence it must be something around this:

 

IF (Table1[Date] = MIN ( Table1[Date] ) ; 100 ; PRODUCT ( Table1[Return] ) * 100

With this filter applied:

FILTER ( ALL ( Table1 ); Table1[Date] <= MAX ( Table1[Date] ) );

VALUES ( Table1[Identifier] )

   

Thank you,

OCK

OCK's avatar
OCK
Frequent Visitor
9 years ago

Anybody who's interested, here's the solution after a few hours of try and error:

 

ReturnTmp =
CALCULATE (  
           IF ( COUNTROWS ( Table1 ) = 1  ;  100  ;  PRODUCT ( Table1 [Return]  ) * 100  ) ;
           FILTER ( ALL ( Table1 ) ;  Table1[Date] <= MAX (Table1[Date]  )  )  ;
           VALUES ( Table1[Identifier] )
)

 

This is a rather standard chart in financial analysis for plotting return series.