Forum Discussion
dax doubt
- 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
Thank you Greg_Deckler.
Finally, thanks to you I archieved it, but I have another problem. I hope you could help me: )
This is my code:
It works perfectly but the problems appears if I have the same date in some rows.
I mean, for example like here:
I have the same dates in three rows so it counts it 3 times.
How can I do it to count it only one??
(I mean, if the id of facture, and date, and base imponible are the same, I would like it to count only once.
I appreciate a lot your help.
Thank you