Hi there,
Thanks for the supporting information, it is very useful!
Nevertheless, I am facing a situation where I do not find a solution “yet”…
I have a list of companies selling fuel materials where unit price value can be expressed either in cents per liter or cents per gallon and for each day there is a selling price which can be repeated if there has been no change for a certain period of time.
Data has been unpivoted this way:
I wish to be able to retrieve the previous different price on a row level. In many cases, it is not equal to previous day (or previous row value) because some companies may maintain their selling price during several days.
I first tried to work on retrieve previous row value and tried the option you listed above:
- Method 1: DAX Index calc column + Previous value calc column, is not working. I guess having repeated rows for each day is messing with the Index logic and so returns incorrect previous row value.
- Method 2: TOPN or DATEADD. TOPN would work if I would filter for only one company but as soon as many companies are filtered it return incorrect previous value. DATEADD seems to work with both filtering options (one or many companies).
- Method Sales Previous Row (Discontinuous Dates): would work if I would filter for only one company but as soon as many companies are filtered it return incorrect previous value.
So previous row value looks good with DATEADD but still the final objective would be to retrieve previous different value (previous different selling price) and I cannot figure out how to write DAX to solve this.
In Power Query, I would not be able to work with Index column due to the repeated same date and so cannot expect to do the same than:
The example commented in the topic Counting how many customers have changed status - #5 by lfPBI was integrating a proper index column which facilitates the calculation… different than my context.
I would love to hear any further suggestion. Thanks.
Alexandre
Demo - Prices.pbix (91.9 KB) Demo - Prices.xlsx (433.2 KB)