Forum Discussion

ania_roh's avatar
ania_roh
Helper III
4 years ago
Solved

dax doubt

Hello I have a problem with DAX code and actually I don´t know how I can solve it. The question is, I have a table with many columns, for example ID factura, cuantity, date of modification, date o...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    ania_roh Sure, here goes. If you provide sample data as text I can be very specific:

     

    Column = 

    /*

        OK, so first you grab the value in the current row that you want to compare with the previous row.

        This may be one column or a couple columns. Also, you might create variables for identifiers that "group"

        different rows. For example, maybe "ID fractura" because you only want to compare rows with the same

        ID for ID fractura

    */
      VAR __Current = [Value]

      VAR __ID = [ID Fractura]

    /*

        OK now you want to figure out the previous row's date, the one just before the current row. So the 

        FILTER gets all rows in the table that have a Date value less than the current row (EARLIER). Now, you 

        could add filters to this using && if you wanted to also filter by something like ID Fractura for example.

        Since you are getting rows with dates less than the current row, the MAXX then returns the maximum

        date in that set, in other words, the date for the event just before the current row. If you wanted the 

        opposite sorting, (look ahead versus look behind) then you would use MINX and > instead of MAXX and 

        <

    */
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
    /*

        Now that you have the date of the previous row, you can grab the Value in that row using the date you 

        calculated. Again, you would add additional filters to the FILTER function using && if you wanted to 

        include ID Fractura for example.

    */
      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])

    /*

        Once you have the current value and previous value, you can do arithmetic on them or compare them.

        In your case, you might do something like IF(__Previous = __Current, 0, 1). This way you could simply SUM

        the column to figure out how many changes there were.

    */
    RETURN
      __Current - __Previous