Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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. 

PortfolioTotal CostCreated_Date
AA1001/17/2023
BB701/23/2023
CC571/18/2023
DD1501/10/2023
AA2502/3/2023
BB302/23/2023
DD70

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-record/td-p/2257400. 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.

PortfolioTotal CostCreated_DateEnd_of_Mont/Current Day
AA1001/17/20231/31/2023
BB701/23/20231/31/2023
CC571/18/20231/31/2023
DD1501/10/20231/31/2023
AA2502/3/20232/28/2023
BB302/23/20232/28/2023
CC571/18/20232/28/2023
DD1501/10/20232/28/2023
AA2502/3/20233/7/2023
BB302/23/20233/7/2023
CC571/18/20233/7/2023
DD703/7/20233/7/2023
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

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