Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Help

**Updated** Hi,   I am trying to script the following requirement into DAX:   Say I invest $1,000,000 (Parameter 1) in a fund in 1999. I have data of the funds "performance" through 1999, hence ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-danhe-msft ,

 

Thanks for your response. 

Sample data : I have messaged you a link to access sample data.

 

Desired result:

With the parameters as mention in the above post.

 

Thanks

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    I am trying to script the following requirement into DAX:

     

    Say I invest $1,000,000 (Parameter 1) in a fund in 1999. I have data of the funds "performance" through 1999, hence I can get the net amount at any point in time by a cummulative calculation. 

    Cummulative performance:

    Growth = 
CALCULATE(
    PRODUCTX(Table, 1 + (Table[Performance]/100)),
    FILTER(ALLSELECTED(Table[AsOfDate]), ISONORAFTER(Table[AsOfDate], MAX(Table[AsOfDate]), DESC)
    )) * Parameter 1

     

    Output for Cummulative performance:

    sample output.PNG

     

    Issue / requirement:

    At the end of each year since 1999, I should withdraw $50,000 (parameter 2) from the total net value at that point in time. With this rule I need to project my investments for the following 20 years (2019) (Date slider).

    The date range has to be flexible. (Slider) 

    Parameter 1 = Amount Invested

    Parameter 2 = Amount withdrawn at the end of each year

     

    Sample Data:

    AsOfDateDataPointDescriptionPerformanceInstrumentDescription
    3/1/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.6795Russell 1000
    3/1/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.6961Russell 3000
    2/28/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.2667Russell 1000
    2/28/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.2713Russell 3000
    2/27/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.0257Russell 1000
    2/27/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.041Russell 3000
    2/26/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.1186Russell 1000
    2/26/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.1622Russell 3000
    2/25/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1435Russell 1000
    2/25/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1273Russell 3000
    2/22/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.6727Russell 1000
    2/22/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.6911Russell 3000
    2/21/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.3465Russell 1000
    2/21/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.349Russell 3000
    2/20/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1782Russell 1000
    2/20/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1987Russell 3000
    2/19/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1555Russell 1000
    2/19/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1686Russell 3000
    2/15/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.0765Russell 1000
    2/15/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.1124Russell 3000
    2/14/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.2117Russell 1000
    2/14/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.1843Russell 3000
    2/13/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.3025Russell 1000
    2/13/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.3031Russell 3000
    2/12/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.2857Russell 1000
    2/12/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.2846Russell 3000
    2/11/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1091Russell 1000
    2/11/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1618Russell 3000
    2/8/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1061Russell 1000
    2/8/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1024Russell 3000
    2/7/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.8789Russell 1000
    2/7/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.8736Russell 3000
    2/6/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.2146Russell 1000
    2/6/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.2093Russell 3000
    2/5/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.4751Russell 1000
    2/5/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.4535Russell 3000
    2/4/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.6927Russell 1000
    2/4/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.7174Russell 3000
    2/1/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1693Russell 1000
    2/1/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1701Russell 3000
    1/31/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.874Russell 1000
    1/31/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.8719Russell 3000
    1/30/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.542Russell 1000
    1/30/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.5068Russell 3000
    1/29/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.1412Russell 1000
    1/29/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.1411Russell 3000
    1/28/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.7293Russell 1000
    1/28/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.722Russell 3000
    1/25/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.9034Russell 1000
    1/25/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.9295Russell 3000
    1/24/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.2111Russell 1000
    1/24/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.2464Russell 3000
    1/23/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.1854Russell 1000
    1/23/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1561Russell 3000
    1/22/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-1.4173Russell 1000
    1/22/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-1.4371Russell 3000
    1/18/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.3088Russell 1000
    1/18/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.2894Russell 3000
    1/17/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.7793Russell 1000
    1/17/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.7856Russell 3000
    1/16/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.2723Russell 1000
    1/16/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.3002Russell 3000
    1/15/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.0508Russell 1000
    1/15/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.0374Russell 3000
    1/14/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.535Russell 1000
    1/14/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.5691Russell 3000
    1/11/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.0073Russell 1000
    1/11/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.0031Russell 3000
    1/10/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.4742Russell 1000
    1/10/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.4735Russell 3000
    1/9/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.4934Russell 1000
    1/9/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.5201Russell 3000
    1/8/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day1.0201Russell 1000
    1/8/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day1.0555Russell 3000
    1/7/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.7888Russell 1000
    1/7/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.8601Russell 3000
    1/4/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day3.4373Russell 1000
    1/4/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day3.4597Russell 3000
    1/3/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day-2.3845Russell 1000
    1/3/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day-2.3456Russell 3000
    1/2/2019 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.0703Russell 1000
    1/2/2019 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.1041Russell 3000
    12/31/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.8843Russell 1000
    12/31/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.8784Russell 3000
    12/28/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day-0.0812Russell 1000
    12/28/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day-0.0391Russell 3000
    12/27/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.8361Russell 1000
    12/27/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day0.7881Russell 3000
    12/26/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day4.9688Russell 1000
    12/26/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day4.9686Russell 3000
    12/24/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day-2.6869Russell 1000
    12/24/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day-2.6342Russell 3000
    12/21/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day-2.0721Russell 1000
    12/21/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day-2.1061Russell 3000
    12/20/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day-1.5934Russell 1000
    12/20/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day-1.6017Russell 3000
    12/19/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day-1.5301Russell 1000
    12/19/2018 0:00Russell 3000 Index TR  - Net Dividends 1 Day-1.5649Russell 3000
    12/18/2018 0:00Russell 1000 Index TR  - Net Dividends 1 Day0.0185Russell 1000

     

     

    I appreciate any help in solving the issue!

     

    Thanks