Forum Discussion
Date difference between clients current and previous row
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!
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?
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 day
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.
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.
DATEDIFF(
Table1[previous.enddate].[Date];
Table1[partition.start].[Date];
DAY
)
Thanks for replying and giving a nudge in the right direction!