Create a Line Graph showing changes to a portfolio over time using the most recent values by month

[Anonymous]

Hello, I've spent quite some time trying to figure this on my own and diving deep into the forums and haven't come away with a clear answer, so I'm hoping someone here can guide me in the right direction.

Problem: Create a Line Graph that shows changes to a portfolio of projects over time using the most recent available entry for a given portfolio at the end of every month and current date.  If a portfolio is not updated in a given month, then the value associated with said portfolio should be pulled from the month prior(s). 

Data 

• Portfolio Column details the specific portfolio
• Total Cost details the planned cost of a portfolio for the entire year at the time a record is created.
• Created_Date is an automated timestamp created when a user goes into the system and creates/updates a given portfolio.

Sample Structure. 

Portfolio	Total Cost	Created_Date
AA	100	1/17/2023
BB	70	1/23/2023
CC	57	1/18/2023
DD	150	1/10/2023
AA	250	2/3/2023
BB	30	2/23/2023
DD	70	3/7/2023

Desired Outcome

Here's what the data should look like after being restructured:

Notice how the Total Cost for portfolios CC and DD stay the same at the end of February because they were not updated in month of Feburary.  Also note that outside of Portfolio DD, all other Portfolio values considered for March are carried from past months prior (with CC's Total Cost being from January when it was last updated). 

Using a line graph, I'd then like to track Total Cost against the End_of_Month/CurrentDay Column. 

What I can already do: I've implemented this logic and can calculate the total cost of the entire portfolio (all projects listed below) at any point in time as long as I select a specific value using this logic: https://community.powerbi.com/t5/Desktop/quot-Rolling-quot-sum-of-a-subgroup-using-the-most-recent-r.... But the line graph is a bit different because I can't preselect a specific value.  

Thanks, I've included sample data of the desired outcome at the bottom of this message as well.

Portfolio	Total Cost	Created_Date	End_of_Mont/Current Day
AA	100	1/17/2023	1/31/2023
BB	70	1/23/2023	1/31/2023
CC	57	1/18/2023	1/31/2023
DD	150	1/10/2023	1/31/2023
AA	250	2/3/2023	2/28/2023
BB	30	2/23/2023	2/28/2023
CC	57	1/18/2023	2/28/2023
DD	150	1/10/2023	2/28/2023
AA	250	2/3/2023	3/7/2023
BB	30	2/23/2023	3/7/2023
CC	57	1/18/2023	3/7/2023
DD	70	3/7/2023	3/7/2023

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBi file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks, @Ashish_Mathur,

Unfortunately, this logic will always take the most recent value and carry it forward.  If I were to group these portfolios at a higher level grouping, then the value would plummet if someone were to make a relatively small update in the future. For example, the value of a group of projects/portfolios is $100M on January 21st. If someone were to update a specific project in that grouping to account for $500K on January 22nd, then the logic run by PowerBI would state that the value of the group of projects and portfolios, which was previously $100M, is now $500K and carry that forward. 

I guess what I'm trying to say is that this logic works at the most granular level, but if you try to go higher, it breaks. Does that make sense?

I had to extract the date from created date field (which was a timestamp) to createa many to one relationship from my data table to the calendar table.

[Ashish_Mathur]

You are welcome.  i do not understand your requirement.  Share a representative dataset, explain the question and show the expected result. 

[Anonymous]

Hi, @Ashish_Mathur , I'll try to be clearer.  

Say that each portfolio belongs to a specific fruit category.

Fruit	Portfolio	Total Cost	Created_Date	End_of_Mont/Current Day
Apples	AA	100	1/17/2023	1/31/2023
Apples	BB	70	1/23/2023	1/31/2023
Orange	CC	57	1/18/2023	1/31/2023
Orange	DD	150	1/10/2023	1/31/2023
Apples	AA	250	2/3/2023	2/28/2023
Apples	BB	30	2/23/2023	2/28/2023
Orange	CC	57	1/18/2023	2/28/2023
Orange	DD	150	1/10/2023	2/28/2023
Apples	AA	250	2/3/2023	3/7/2023
Apples	BB	30	2/23/2023	3/7/2023
Orange	CC	57	1/18/2023	3/7/2023
Orange	DD	70	3/7/2023	3/7/2023

At the end of March, the value for "Orange" should be 127 - but the return when you graph the values, you on get 70 - ignoring the 57 from CC.  Apple is now 30 instead of 280. 

So the logic works when we just worry about portfolios, but summations are incorrect when you try to go a level higher.

Sample Structure. 

Fruit	Portfolio	Total Cost	Created_Date
Apple	AA	100	1/17/2023
Apple	BB	70	1/23/2023
Orange	CC	57	1/18/2023
Orange	DD	150	1/10/2023
Apple	AA	250	2/3/2023
Apple	BB	30	2/23/2023
Orange	DD	70	3/7/2023

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBi file from here.

Hope this helps.

[Anonymous · Accepted solution]

Thanks, this is it. So folks are aware or the OneDrive link inevitably dies, here's the logic. 

You need to create a calendar table (date dim) 

Calendar = CALENDAR(MIN(Data[Created_Date]),EOMONTH(MAX(Data[Created_Date]),0))

Sum your cost from the Data Table

TC = SUM(Data[Total Cost])

Use the following measure:

Measure = CALCULATE([TC],LASTNONBLANK(CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],minx(ALL('Calendar'),'Calendar'[Date]),max('Calendar'[Date]))),CALCULATE([tc])))

Lastly, do the following:

Measure 3 = SUMX(CALCULATETABLE(VALUES(Data[Portfolio]),all('Calendar')),[Measure])

I had to replace the VALUES(Data[Portfolio]) with SUMMARIZE(Data, Data[Column 1], Data[Column 2], etc) to get what I wanted. 

[Ashish_Mathur]

You are welcome.