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.
Dynamically Pick Min Date based on slicer's selection
Hi friends, I am still new to Power BI, hope you guys can provide me some guidance. Here is my situation: I would like to pick min date for each level of percentile (low, mid, and high) from each provider separately based on the condition that the percentage in PctAvailable column is greater than the corresponding percentage in slicer (on the leftside of the screenshot below). I created three columns, LowDate, MidDate, and HightDate to store the calculated min dates. Also, I added columns LowPCT, MidPCT, and HighPCT to show the selected percents from slicers for demenstration purposes. Using provider 403 as example, the LowDate is 10/13, MidDate is 12/20, and HighDate is also 12/20 based on the current selections. However, the min dates of each level don't change when different values are selected. It looks like the min dates are selected using the min percentage of each level, not dynamically update based on the selection. I created three tables containing the values of each slicer, called LowSelection, MidSelection, and HighSelection. Here are the formulas that I used for the calculation (using Low percentile calcs as example) LowPCT= if(HASONEFILTER(LowSelection[Low]),LOOKUPVALUE(LowSelection[Low],LowSelection[Low],values(LowSelection[Low]))) LowDate= CALCULATE(min('Table'[SlotDatetimeDTS]),FILTER('Table','Table'[ProviderID]=EARLIER('Table'[ProviderID])&&'Table'[PctAvailable]>[LowPCT])) Please let me know which parts do I have fix or adjust? Thank you so much for your time!
Add column that will display the first / new load date that the row appeared
Hi Good Day, I have this table that shows ID, Load Date, Product # and Section. I want to add a column that will display the first load date that the row appeared, but if the row will not appear on the next load date (or will skip a date) and will apear again on the next-next load date, the date that must appear is the new load date. This is my desired output: I tried using this measure, Date Appeared = VAR firstload = Sheet1[Load Date] VAR mindate = CALCULATE(MIN(Sheet1[Load Date]), ALLEXCEPT((Sheet1), Sheet1[Product No], Sheet1[Section])) RETURN IF(Sheet1[Load Date] = CALCULATE(MIN(Sheet1[Load Date]), ALLEXCEPT((Sheet1), Sheet1[Product No], Sheet1[Section])), firstload, mindate) and I got this result, I find it hard to display the new load date. Hope that you help me with this. Thanks a lot..
