'Difference between multiple dates in the same column based on category
I have the following problem ..
I have this table:
I want to create a calculated column that tells me the difference of days between two dates of the same code (COD), the difference should be calculated based on the date before the line.
For example:
Using COD B
COD | DATE | days_diff
B |05/01/2018 |
B |09/01/2018 | 4
B |12/01/2018 | 3
In the example image the codes / dates are sorted in sequence, but in reality they are out of order.
I tried to use the following sentence in DAX:
DATEDIFF(Testing[DATE]; FIRSTDATE(FILTER( ALL(Testing[DATE]) ;Testing[DATE] > EARLIER(Testing[DATE])));DAY)
Explaining what I've tried:
Make the difference between the date on the line and using the EARLIER function to get the most recent date out of the current one.
However, I obtained the following result:
I can not filter COD, so that the analysis of 'EARLIER' is performed only in the same 'group', so I understand the PowerBI is considering all the dates.
Any idea?
Solution 1:[1]
You're pretty close in idea, but it needs some tweaking. Try this:
Days_diff =
VAR StartDate =
CALCULATE (
LASTDATE ( Testing[DATE] ),
FILTER (
ALLEXCEPT ( Testing, Testing[COD] ),
Testing[DATE] < EARLIER ( Testing[DATE] )
)
)
RETURN
DATEDIFF ( StartDate, Testing[DATE], DAY )
The variable StartDate
calculates the last date before the current row date. I use CALCULATE
to remove all row context except for COD
since that is what we are grouping on.
Note: The EARLIER
function is not a date/time function, but rather a reference to an earlier row context (before stepping inside the FILTER
function). It allows us to jump back a level when we are nesting functions.
Then you just take the DATEDIFF
.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Alexis Olson |