Forum Discussion
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 of facture, etc.
This is a table where you can see all the modifaction of the facturation.
I would like to write a code where I see how many times the cuantity and date of facture changed. I mean:
firstly I would like to have it ordered by date of modifaction. And then, if I have it from the oldest to newest, then I would like to go from one row to another, to see if there is a change in the column of date of facture or cuantity and count this change.
I know that I have to use earlier, and I know that should use countrows to count how many times changed everything. But I got lost with sorting and I don´t actually understand how I should use it.
Help please!
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
6 Replies
ania_roh I would recommend a calculated column based on the MTBF pattern. 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 - __PreviousHello Greg_Deckler, thank you for your quick answer.
Can you explain me every step you wrote?
Im not very avanced so I don´t understand what you meant
Thank you
And actually I don´t understand if it sorts from the oldest to newest.
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 - __PreviousThank 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
