Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lesveilleurs
Frequent Visitor

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 SampleExcel SampleCurrent PowerBi fail line chartCurrent 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()
)

------------------------
1 ACCEPTED SOLUTION

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))

lbendlin_0-1713742799542.png

 

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hi lbendlin

Thanks for looking at the question!

 

Wondering if the sample has been provided is not enough, or is it not able to be accessed?

 

Sample: Sample Power Bi & Raw Data

You may be overcomplicating this a little? Do you really need to add the initial "100"  value?  What do you mean by "umbrella" ?

 

lbendlin_0-1713731579679.png

 

 

 

Hi lbendlin,

 

Sorry for confusing you, the umbrella represents the outcome's look.

We like to have them all start from 100, to analysis and compare the performance of them, and for people easily understand how it is, sometimes, will use Usd 1,000 instead.

Here are some application examples.

 

Total return - Sample 1 

Total return - Sample 2  

 

 

Screenshot_20240422-070453_Chrome.jpg

 

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))

lbendlin_0-1713742799542.png

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors