cancel
Showing results 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

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

------------------------
1 ACCEPTED SOLUTION
Super User

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

Frequent Visitor

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

Super User

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

Frequent Visitor

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.

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors