cancel
Showing results for
Did you mean:
Frequent Visitor

## Percent Return for last 5 trading days (Base Price as of Earliest Date)

How can I make PxBase (base price) the same for all cells (according to earliest day price)

In Tableau, I would do this.  How do I do it in Power Bi?
{ FIXED [Ticker] : SUM( IF [Date.Index] = [MinDate] THEN SUM([Close) END) }

My current measure formula are as below.  The raw date range is from 2020 to 2022, thus I created an index column in the table to calculate the last 5 trading days for each ticker (dates are non-continious due to weekends and holidays in different stock markets).

``````PxCurrent =
VAR MaxIndex = CALCULATE( MAX(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y,IndexETF_L2Y[Ticker]) )
VAR CurIndex = CALCULATE( MIN(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y, IndexETF_L2Y[Ticker], IndexETF_L2Y[Date.Index]) )
VAR Period = 5
VAR Counter = MaxIndex - CurIndex
VAR CurPx     = CALCULATE( SUM(IndexETF_L2Y[Close]), FILTER(IndexETF_L2Y, Counter <= Period) )
RETURN
CurPx``````

``````PxBase =
VAR MaxIndex = CALCULATE( MAX(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y,IndexETF_L2Y[Ticker]) )
VAR CurIndex = CALCULATE( MIN(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y, IndexETF_L2Y[Ticker], IndexETF_L2Y[Date.Index]) )
VAR Period = 5
VAR Counter = (MaxIndex - CurIndex)
VAR StartPx   = CALCULATE( SUM(IndexETF_L2Y[Close]), FILTER(IndexETF_L2Y, Counter = Period) )
RETURN
StartPx``````

Ultimately, I want to divide PxCurrent / PxBase - 1 in order to get to the last 5 days percent change (pegged to starting date) chart like this:

Grateful for any Power Bi Master's help.

1 ACCEPTED SOLUTION
Super User

@ALC9816 , if 5 days data is already filtered and PX base is a measure

calculate(firstnonblankvalue(Table[Date], [PxBase]), allselected(Table))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
2 REPLIES 2
Super User

@ALC9816 , if 5 days data is already filtered and PX base is a measure

calculate(firstnonblankvalue(Table[Date], [PxBase]), allselected(Table))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

Thank you for the quick reponse, and the good idea on FirstNonBlankValue.  I tweaked the solution a bit to accomodate to my data layout as follow:

calculate(firstnonblankvalue(Table[Date], [PxCurrent]), allexcept(Table,[Ticker]))

The resulting table looks like this:

For any one out there looking for this solution, please see code below:

``````PxChg.L5d =
VAR MaxIndex = CALCULATE( MAX(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y,IndexETF_L2Y[Ticker]) )
VAR CurIndex = CALCULATE( MIN(IndexETF_L2Y[Index]), ALLEXCEPT(IndexETF_L2Y, IndexETF_L2Y[Ticker], IndexETF_L2Y[Date.Index]) )
VAR Period = 5  //Adjust for different lookback period
VAR Counter = MaxIndex - Period
VAR CurPx = CALCULATE( SUM(IndexETF_L2Y[Close]), FILTER(IndexETF_L2Y, MIN(IndexETF_L2Y[Index]) >= Counter) )
VAR BasePx = CALCULATE( FIRSTNONBLANKVALUE(IndexETF_L2Y[Date.Index],
IF(MIN(IndexETF_L2Y[Index]) >= Counter, SUM(IndexETF_L2Y[Close]))),
ALLEXCEPT(IndexETF_L2Y,IndexETF_L2Y[Ticker])
)
RETURN
IF( MIN(IndexETF_L2Y[Index]) >= Counter,
DIVIDE( CurPx, BasePx ) - 1
)    ``````

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors