Forum Discussion
Create a Line Graph showing changes to a portfolio over time using the most recent values by month
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.
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.
Super UserYou are welcome. i do not understand your requirement. Share a representative dataset, explain the question and show the expected result.
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
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.