Forum Discussion
Helper VComparison against Previous Record
Hello,
I'm new with DAXand my logic is still with Excel.
Using Calculated Column with DAX (not in Power Query), how do I check my current record against previous record?
| Data | Desired Result |
A | 1 |
| A | 1 |
| B | 2 |
| B | 2 |
| B | 2 |
| C | 3 |
| C | 3 |
| D | 4 |
| E | 5 |
| F | 6 |
| F | 6 |
The logic that I want here is that if I'm the 1st record, then assign value 1 otherwise check against previous record, and if previous record is the same as current record, take the above assign value otherwise add 1
4 Replies
- Greg_Deckler
Community Champion
JustDavid See my article on Mean Time Between Failure (MTBF) which uses EARLIER: 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 
JustDavidThanks for the reply Greg_Deckler .
I have questions regarding your DAX formula.
Using my table above as example,
- Would this VAR __Current = [Desired Result] return the current value? If it is, my question would then be, how does it know to assign a value 1 to the 1st record and then either stay or add a counter?
- VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date]). I'd assume your 'Date' here refers to my 'DATA' column? And if it is, then would MAXX works with string data type?
- Greg_Deckler
JustDavid You are going to need to add an Index to your table to be able to let DAX know what "before" is.
- JustDavid
Sorry...I don't quite understand.
Even if I were to add an index column (I'd assume starting from 0), in which formula do I use it in?
Is it in VAR __PreviousData = MAXX(FILTER('Table','Table'[DATA] < EARLIER('Table'[DATA])),[INDEX])?
Again. How do I assign value 1 to the 1st record of the column 'Desired Result'?
Just to be clear, This is the logic that I had with Excel.
- Put a value 1 in the 1st record in column 'Desired Result'.
- Add a counter: Add a counter to 'Desired Result' column from the previous record of [DESIRED RESULT] IF AND ONLY IF when the current record [DATA] is not the same as the previous record [DATA] i.e. Use previous row value from the 'Desired Result' + 1 If current record [DATA] is NOT the same as previous record of the [DATA].
- Do not add counter: Take the value from 'Desired Result' column of the previous record of [DESIRED RESULT] IF AND ONLY IF when the current record [DATA] is the same as the previous record [DATA] i.e. Use previous row value from the 'Desired Result' If current record [DATA] is the same as previous record of the [DATA].
