topic Re: Percent Return for last 5 trading days (Base Price as of Earliest Date) in DAX Commands and Tips

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

ALC9816 — Wed, 29 Jun 2022 03:56:38 GMT

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.

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

amitchandak — Wed, 29 Jun 2022 10:08:50 GMT

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

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

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

ALC9816 — Thu, 30 Jun 2022 08:14:57 GMT

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 )

Thanks again, amitchandak. Will subscribed and like your youtube channel. Grateful!