Forum Discussion

Lesveilleurs's avatar
Lesveilleurs
Frequent Visitor
2 years ago
Solved

Line Chart with Multiple Values Starting from $100 USD (Calculated from portfolio's Start Month)

Hi everyone,

 

 

I have some monthly portfolio performance data on hand, along with the starting month for each portfolio.
Additionally, I have historical monthly performance data for 3 benchmarks from a long time ago until latest Month (Today).

 

I would like to create a line chart with the following specifications, which I've serach & tried so many DAX, but all fail:

 

* Total returns on $100 USD: Set the starting point for each portfolio as 100 from one month before each portfolio's start month.

* Select one Porfolio at a time the line chart should display that portfolio and all 3 benchmarks, all starting from 100.

* The next month's number for each line should be calculated as

  • 2nd Mth (Profolio Start Month)#A =  100 * (1 + 2nd Mth portfolio return rate / 100).
  • 3rd Mth #B =  A *  (1 + 3rd Mth portfolio return rate / 100).
  • 4th Mth #C =  B *  (1 + 4th Mth portfolio return rate / 100).
  • etc....

The line chart should resemble an umbrella.

 

I have attached a sample PBIX file for your reference, along with an explanation of what this line chart exactly achieves for your reference.

 

Sample: Sample Power Bi & Raw Data

 

 

Thank you very much

 

Excel SampleCurrent PowerBi fail line chart

 

----------------

Current Fail DAX: 

 

Measure =

VAR first_date = CALCULATE(MIN('Data'[Date]),ALLSELECTED('Data'))
VAR last_date = MAX('Data'[Date])
VAR mandate = MAX('Data'[Name])
VAR FilteredTable =
FILTER(
ALL('Data'),
'Data'[Date] <= last_date &&
MONTH('Data'[Date]) = MONTH(last_date) &&
'Data'[Name] = mandate
)
VAR FilteredTable_2 =
FILTER(
ALL('Data'),
'Data'[Date] <= last_date &&
'Data'[Date]> first_date &&
'Data'[Name] = mandate
)


VAR HasData = NOT(ISBLANK(SUMX(FilteredTable, 'Data'[Mothly Return (%)])))
RETURN
IF(HasData,
IF(MIN('Data'[Date])=first_date,100,
100*PRODUCTX(FilteredTable_2,1+[Mothly Return (%)]/100)
)
,BLANK()
)

------------------------
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Then you will need to use a disconnected Calendar table and modify the measure slightly.

    Measure = 
var md = max('Calendar'[Date])
var mn = max(Master[Name])
var mnd = minx(filter(Data,[Name]=mn),[Date])
return if(edate(mnd,-1)=md,100, 100*productx(filter(allselected(Data),[Name]=mn && [Date]<=md),1+[Mothly Return (%)]/100))

     

     

5 Replies