Difference between multiple dates in the same column

Question

I have the following problem..

I have the following 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 shall be calculated based on the previous date of the line. For example:

Using the COD B

COD | DATE | Days of Difference

B |05/01/2018 |

B |09/01/2018 | 4

B |12/01/2018 | 3

In the image example the codes/dates are ordered in sequence, but in reality they are out of order.

I tried using the following sentence on DAX:

DATEDIFF(Testing[DATE]; FIRSTDATE(FILTER( ALL(Testing[DATE]) ;Testing[DATE] > EARLIER(Testing[DATE])));DAY)

Explaining what I tried: Make the difference between the date on the line and using the function EARLIER catch the latest date off the current.

However, I got the following result:

I’m not getting to put as filter the COD, so that the analysis of 'EARLIER' is performed only in the same 'group', so I understand Powerbi is considering all dates.

Any idea?

Answer 1

After a few attempts, I asked also in the OS. And I got the following solution that worked very well, and can be found completely here:

Difference between Multiple Dates in the same column based on Category

That is the solution:

Days_diff =
VAR StartDate =
    CALCULATE (
        LASTDATE ( Testing[DATE] ),
        FILTER (
            ALLEXCEPT ( Testing, Testing[COD] ),
            Testing[DATE] < EARLIER ( Testing[DATE] )
        )
    )
RETURN
    DATEDIFF ( StartDate, Testing[DATE], DAY )

And the explanation provided by the user Alexis..

"The variable Startdate calculates the last date before the date of the current line. I use the CALCULATE to remove the entire line context, except COD, since this is what we are grouping together.

Note: The function EARLIER is not a date/time function, but a reference to a previous line context (before entering the function FILTER). This allows us to return a level when we are nesting functions.

So you just take the DATEDIFF.