Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to calculate and visualize cummulative returns for Funds (Ex: S&P 500).
I have daily performance data for the fund.
Desired Output:
Making the measure dynamically start from 0% for the selected date range and then calculate returns from there.
Example:
Challenge/Issue:
I would greatly appreceate any help in making the calculation start from 0 for any selected date range.
This is where I am at currently:
Return =
CALCULATE(
PRODUCTX(Table, 1 + (Table[DataPointValue]/100))
, FILTER(ALLSELECTED(Table[AsOfDate]), ISONORAFTER(Table[AsOfDate], MAX(Table[AsOfDate]), DESC)
)) - 1
Sample Data:
AsOfDate | Fund | Daily Performance |
4/28/2020 0:00 | S&P 500 Index | -0.52% |
4/27/2020 0:00 | S&P 500 Index | 1.47% |
4/24/2020 0:00 | S&P 500 Index | 1.39% |
4/23/2020 0:00 | S&P 500 Index | -0.05% |
4/22/2020 0:00 | S&P 500 Index | 2.30% |
4/21/2020 0:00 | S&P 500 Index | -3.07% |
4/20/2020 0:00 | S&P 500 Index | -1.79% |
4/17/2020 0:00 | S&P 500 Index | 2.68% |
4/16/2020 0:00 | S&P 500 Index | 0.58% |
4/15/2020 0:00 | S&P 500 Index | -2.20% |
4/14/2020 0:00 | S&P 500 Index | 3.07% |
4/13/2020 0:00 | S&P 500 Index | -1.01% |
4/9/2020 0:00 | S&P 500 Index | 1.46% |
4/8/2020 0:00 | S&P 500 Index | 3.43% |
4/7/2020 0:00 | S&P 500 Index | -0.16% |
4/6/2020 0:00 | S&P 500 Index | 7.03% |
4/3/2020 0:00 | S&P 500 Index | -1.50% |
4/2/2020 0:00 | S&P 500 Index | 2.30% |
4/1/2020 0:00 | S&P 500 Index | -4.41% |
3/31/2020 0:00 | S&P 500 Index | -1.59% |
3/30/2020 0:00 | S&P 500 Index | 3.37% |
3/27/2020 0:00 | S&P 500 Index | -3.37% |
3/26/2020 0:00 | S&P 500 Index | 6.25% |
3/25/2020 0:00 | S&P 500 Index | 1.15% |
3/24/2020 0:00 | S&P 500 Index | 9.39% |
3/23/2020 0:00 | S&P 500 Index | -2.93% |
3/20/2020 0:00 | S&P 500 Index | -4.32% |
3/19/2020 0:00 | S&P 500 Index | 0.48% |
3/18/2020 0:00 | S&P 500 Index | -5.18% |
3/17/2020 0:00 | S&P 500 Index | 6.00% |
3/16/2020 0:00 | S&P 500 Index | -11.98% |
Thanks!
@sv12 , when every you do such calculation use date table
Return =
CALCULATE(
PRODUCTX(Table, 1 + (Table[DataPointValue]/100))
, FILTER(ALLSELECTED(DATE[AsOfDate]), ISONORAFTER(DATE[DATE], MAX(DATE[DATE]), DESC)
)) - 1
I used like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |