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 direc...
General Comment
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    You may download my PBi file from here.

    Hope this helps.

  • 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. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

Say that each portfolio belongs to a specific fruit category.

 

Fruit

PortfolioTotal CostCreated_DateEnd_of_Mont/Current Day
ApplesAA1001/17/20231/31/2023
ApplesBB701/23/20231/31/2023
OrangeCC571/18/20231/31/2023
OrangeDD1501/10/20231/31/2023
ApplesAA2502/3/20232/28/2023
ApplesBB302/23/20232/28/2023
OrangeCC571/18/20232/28/2023
OrangeDD1501/10/20232/28/2023
ApplesAA2502/3/20233/7/2023
ApplesBB302/23/20233/7/2023
OrangeCC571/18/20233/7/2023
OrangeDD703/7/20233/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. 

FruitPortfolioTotal CostCreated_Date
AppleAA1001/17/2023
AppleBB701/23/2023
OrangeCC571/18/2023
OrangeDD1501/10/2023
AppleAA2502/3/2023
AppleBB302/23/2023
OrangeDD70

3/7/2023

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

You may download my PBi file from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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. 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      You are welcome.