Dynamic Cumulative Portfolio Return - With Files

[Power_BI_Help]

Hello Power BI Communities,

I am trying to create a cumulative return plot for multiple portfolios (split out via legend) vs a time series axis. I want to be able to drill down into a specific year/month/quarter/etc from and have the cumulative performance reset for the viewed timeframe on the graph or be able to use a slicer/filter to crop down the area that I am looking at and still have the cumulative performance for the visible data start at 0%.

Cumulative Return is defined here: https://www.investopedia.com/terms/c/cumulativereturn.asp

I am able to calculate the sum of the daily change for the period I am looking at. Where I am running into difficulties is finding a way to reference the initial portfolio value (fix) across the entire time series. Historically, for year over year return or similar I am able to use FIRSTDATE and pull the beginning portfolio value for that time unit. Cumulative is a bit more challenging because you need that value to be fixed across the time units. Any formula I incorporate or calculate will iterate over the timeframe that I am on (i.e. if I want to use the portfolio value for 1/1/2015 across all the time series the formulas are using 1/1/2015, 1/2/2015, 1/3/2015, etc not a column populated with the portfolio value at 1/1/2015). I am thinking this might be possible with FIRSTNONBLANK, ALLSELECTED, FILTER, EARLIEST functions but I can't seem to piece it together. 

I attached an example Power BI file that has example portfolio data trying to illustrate what I am looking for. Additionally, I attached an excel file that has the appropriate calculation for cumulative return starting at different time points (which in Power BI would be calculated correctly via a slicer or filter).

Here is a link to the Onedrive files: https://1drv.ms/u/s!AqA0zdOdqkphhUrRMiu9lSWhkFez?e=2edupu

[v-shex-msft]

Hi @Power_BI_Help,

It sounds like a general record analytic across multiple date fields that not supported on power bi desktop side.
I'd like to suggest you take a look at following link about create a calculated table with expand the date range records and use it as bridge table to link raw tables.
After these steps, you can use bridge date field and raw table fields to simply analyst records between two date ranges.

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

[Power_BI_Help]

Hey Xiaoxin Sheng / @v-shex-msft ,

The concept here is to create an independent table where each of the desired viewing ranges is an independent row. Each row will contain a singular portfolio value. That way, anytime you reference that row a function will obtain the singular value which is not tied to a date? 

Thanks for your reply, it is unfortunate that a global variable/general record is not available.

[Power_BI_Help · Accepted solution]

For anyone else who reads this... I should have thought of the problem in a different way, silly to keep it in raw dollars.

I ended up calculating daily change ($); then converting that into a daily percent change (%); 1 + daily percent change (%).  That eliminates the need to be able to divide by the first portion because you can keep your units in percentages. Then run a Productx (Table, Table[PercChg]). Cumulative will start at 100% and adjust based on gain/loss going forward.

[amitchandak]

@Power_BI_Help , I have not checked the file. But if you want diff between the first and last selected date you can do like

measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate(max(Table[price]), filter(Table,Table[date]=_max)) -calculate(max(Table[price]), filter(Table,Table[date]=_min))

[Power_BI_Help]

Hey Amitchandak I appreciate the response,

That is a clever way to calculate the cumulative from a KPI perspective, but I am trying to figure out a way to have a Line Chart which shows all the values in between. In the formula written, it filters to the _max & _min dates (so you filter out all the in-between data points.

I want this to be a fixed (static) value throughout the entirety of the calculation for all time points:

var _min = Calculate(Max(Table[Price]),minx(allselected(Date),Date[Date]))

// the problem is when you apply that expression as a filter it limits the data to only the min date and nothing in between