Forum Discussion
Portfolio Value Over Time
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 |
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
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 - _SUM2Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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
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 - _SUM2Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.