Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@ALC9816 , if 5 days data is already filtered and PX base is a measure
calculate(firstnonblankvalue(Table[Date], [PxBase]), allselected(Table))
@ALC9816 , if 5 days data is already filtered and PX base is a measure
calculate(firstnonblankvalue(Table[Date], [PxBase]), allselected(Table))
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |