cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StanTheMan
Frequent Visitor

Date difference between clients current and previous row

Hi all,

I'm looking for a solution to get the date difference between the current row and previous row for every unique client. Below is a very small example of what my dataset looks like for a single client. I would like to make a new column that calculates the difference for every client between his first row to the second row, the second to third row, etc. 

Sample.JPG

Currently i'm trying to cook up something with the MAX or EARLIER dax functions, but not with much succes yet.

I'm curious to see what you guys can think up for this!

Thanks in advance,
Stan

1 ACCEPTED SOLUTION

Hi,

I solved the issue with some more research and luck. The sollution is as followed, after creating the Partition.Index as explained previously, I first create a column that calculates the previous end date.

previous.enddate =
Calculate(
MIN([partition.end]);
FILTER(Table1; Table1[partition.index] + 1 = Earlier(Table1[partition.index]));
FILTER(Table1; Table1[id] = EARLIER(Table1[id]))
)

This give the previous end date/row from the current id (if unavailable, returns blank). Finally with a simple DATEDIFF calculation I calculate the time between the previous enddate and current startdate and that's pretty much it.

Daysbetween=
DATEDIFF(
Table1[previous.enddate].[Date];
Table1[partition.start].[Date];
DAY
)

Thanks for replying and giving a nudge in the right direction!

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @StanTheMan ,

 

One sample for your reference, Please check the following steps.

 

1, Insert index column by id column. M code for your reference.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5RCsAgDAPQu/Rb0HS6rWcR738NmUPaZX+hr5D0LpAkyMhaYCsebxxpW3UD2+3W2C43+9rpfeumsfiJ6v/K+4ythX1gtICVUf/rxwQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"end", type date}, {"start", type date}, {"id", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"id"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"start", "end", "Index"}, {"Partition.start", "Partition.end", "Partition.Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Partition.Index", Int64.Type}, {"Partition.start", type date}, {"Partition.end", type date}})
in
    #"Changed Type1"

Capture.PNG

 

2. To create two calculated column to get the excepted result we need.

 

start = 
VAR ind = Table1[Partition.Index] - 1
VAR pre =
    CALCULATE (
        MAX ( Table1[Partition.start] ),
        FILTER ( Table1, Table1[Partition.Index] = ind ),
        VALUES ( Table1[id] )
    )
RETURN
    IF (
        ISBLANK ( Table1[Partition.start] ),
        BLANK (),
        IF ( ind = 0, 0, DATEDIFF ( pre, Table1[Partition.start], DAY ) )
    )
end = 
VAR ind = Table1[Partition.Index] - 1
VAR pre =
    CALCULATE (
        MAX ( Table1[Partition.end] ),
        FILTER ( Table1, Table1[Partition.Index] = ind ),
        VALUES ( Table1[id] )
    )
RETURN
    IF (
        ISBLANK ( Table1[Partition.end] ),
        BLANK (),
        IF ( ind = 0, 0, DATEDIFF ( pre, Table1[Partition.end], DAY ) )
    )

 

re.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @v-frfei-msft 

Thanks for replying! At first glance the solution looked good to me, but upon further looking and trying to apply the solution on my own data set i've ran into some problems, see my screenshot. Can you help me by explaining the solution further?

Dataset example.JPG


I personally think its related to an error in my DAX Columns, they currently calculate way higher date differences than in your the example. (Directly copied your formulas and adjusted my own tablenames and columns) Maybe I'm misunderstanding what the columns calculate, so can you please explain to me what these columns calculate?

If i take my example from above, I'm expecting to see one new column that calculates the difference in days between the partition.index [1] -> partion.end [31-3-2018] and partion.index [2] -> partion.start [1-4-2018] = 1 dayexpected.png

I hope you can help me, thanks in advance!
Stan

Hi,

I solved the issue with some more research and luck. The sollution is as followed, after creating the Partition.Index as explained previously, I first create a column that calculates the previous end date.

previous.enddate =
Calculate(
MIN([partition.end]);
FILTER(Table1; Table1[partition.index] + 1 = Earlier(Table1[partition.index]));
FILTER(Table1; Table1[id] = EARLIER(Table1[id]))
)

This give the previous end date/row from the current id (if unavailable, returns blank). Finally with a simple DATEDIFF calculation I calculate the time between the previous enddate and current startdate and that's pretty much it.

Daysbetween=
DATEDIFF(
Table1[previous.enddate].[Date];
Table1[partition.start].[Date];
DAY
)

Thanks for replying and giving a nudge in the right direction!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors