Dynamic Tables

[Anonymous]

Hi All,

I need to create a measure to return Yearly return of diffrent funds. The formual for yearly return is actually simple for example if the inception date was 5/6/2017 I need the following report

Date                                           Value 

2017                (return on 12/31/2017 / return on 5/6/2017)  -1

2018                (return on 12/31/2018 / return on 1/1/2018)  -1

2019                (return on 12/31/2019 / return on 1/1/2019)  -1

2020                (return on 04/13/2020 (today) / return on 1/1/2020)  -1

I cannot use set dates because funds are selected via a slicer and each fund might have a diffrent inception date ( and year) . It's easy to generate a list of years for each fund based on slicer selection (selcted fund) but the problem I have I don't know how to use this list for gerenating the above calculation. I'm not sure if we have the concept of temp table in DAX for holding the list of years? or there is another way of doing this type of dynamic calculations? 

[Anonymous]

Here's how to build correct models:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

No. DAX is not SQL. There are no temporary tables.

By the way, what will your calculation look like if you select 2 different funds with different start dates? You only showed an example with one start date but that's not enough.

Best
D