Forum Discussion
Calculated Column to Retrieve previous date where value was different (Filtered by criteria)
Hi all,
I am attempting to find previous last_run date where the position value was different in a records table based on the ID and product being the same. So following the table below, when the position value column was last different and have that shown in a table as a calculated column.
| id | position | product | last_run | Desired Column - Previous Position | Desired Column - Previous date |
| 0012000000bkPfwAAE | Bottom | Product A | 01-Sep-24 | Middle | 01-Jun-24 |
| 0012000000bkPfwAAE | Middle | Product A | 15-Aug-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Aug-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product A | 15-Jul-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product A | 09-Jul-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product A | 15-Jun-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Jun-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Top | Product A | 01-May-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Top | Product A | 01-Apr-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Top | Product A | 01-Mar-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Feb-24 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Jan-24 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Dec-23 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product A | 01-Nov-23 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Bottom | Product B | 01-Sep-24 | Middle | 01-Jun-24 |
| 0012000000bkPfwAAE | Middle | Product B | 15-Aug-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Aug-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product B | 15-Jul-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product B | 09-Jul-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product B | 15-Jun-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Jun-24 | Top | 01-May-24 |
| 0012000000bkPfwAAE | Top | Product B | 01-May-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Top | Product B | 01-Apr-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Top | Product B | 01-Mar-24 | Middle | 01-Feb-24 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Feb-24 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Jan-24 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Dec-23 | Middle | 01-Nov-23 |
| 0012000000bkPfwAAE | Middle | Product B | 01-Nov-23 | Middle | 01-Nov-23 |
I have attempted using MAX function in a calculated column but I dont think I am getting the variables right.
Would be great to get your assistance 🙂
Thank you
1 Reply
- Greg_Deckler
Community Champion
CRanjan12 See my article on Mean Time Between Failure (MTBF) which uses EARLIER. In this case, you would just need to stop at PreviousDate variable (see below). And of course include your ID and Product name in your filter.
http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.
