Portfolio Value Over Time

[Jordan-Adrian]

I have a Projects table containing a potfolio of projects. Each project has a Start Date and an End Date as well as a Total Value. Although I do have a date table, I dont use it often because I use mutliple date filters based on multiple date columns, all requiring a custome financial year, so I find it easier to create fiscal year and quarter columns, as caculate columns, for each date in the Projects table - I'm not sure how relevant this is but wanted to mention it. Regardless, I have an active date table relationship with the Start Date and an inactive date table relaionship with the End Date. 

I'd like to create a calculation that sums the total value of the portfolio over time. So in a line graph, for any given day, I want to see the sum Total Value of all Start Dates on or before the given day minus the sum Total Value of all End Dates on or before that given day. I need this to react to both my Start Date and End Date filters - which I currently have setup using my Start Date and End Date columns directly in the Project table. 

Here is a sample table:

ID	Project Name	Start Date	End Date	Lastest USD Value
8	Iron Man	6/28/2020	6/28/2023	100000
9	Batman	2/14/2021	2/14/2022	40000
42	Spiderman	5/5/2020	5/5/2022	10000
52	Hulk	10/1/2020	1/1/2022	1000000
57	Wolverine	7/1/2023	7/1/2025	750000
79	Daredevil	9/1/2019	9/1/2023	60000
90	Superman	1/1/2021	5/1/2021	100000
93	Wonder Woman	3/23/2022	3/1/2024	35000
133	Thor	5/9/2022	5/14/2026	2000000

[Anonymous · Accepted solution]

Hi @Jordan-Adrian ,

According to your statement, I think you can try code as below to create a measure.

Measure = 
VAR _SELECTION = SELECTEDVALUE('Calendar'[Date])
VAR _SUM1 = CALCULATE(SUM('Table'[Lastest USD Value]),FILTER('Table','Table'[Start Date]<=_SELECTION))
VAR _SUM2 = CALCULATE(SUM('Table'[Lastest USD Value]),FILTER('Table','Table'[End Date]<=_SELECTION))
RETURN
_SUM1 - _SUM2

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @Jordan-Adrian 

If i understood you correctly you can use a disconnected dates table 

And dax 

portfolio_value =

VAR selectedDate = MAX('calendar'[Date])

RETURN

SUMX('portfolio',

VAR StartDate = [Start date]

VAR EndDate = [End Date]

RETURN IF(StartDate<= selectedDate && OR(EndDate>=selectedDate, EndDate=BLANK() ),[Lastest USD Value],0)

)

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly