Forum Discussion

JustDavid's avatar
JustDavid
Icon for Helper V rankHelper V
3 years ago

Comparison 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 ...
Need Help
JustDavid's avatar
JustDavid
Icon for Helper V rankHelper V
3 years ago

Thanks for the reply Greg_Deckler .

 

I have questions regarding your DAX formula.

 

Using my table above as example, 

  1. 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?
  2. 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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

JustDavid You are going to need to add an Index to your table to be able to let DAX know what "before" is.

  • JustDavid's avatar
    JustDavid
    Icon for Helper V rankHelper V
    3 years ago

    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.

    1. Put a value 1 in the 1st record in column 'Desired Result'.
    2. 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].
    3. 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].