Calculate the customer time interval in a row

[ljmanayon]

Hi All,

Good day!

I've been searching for a solution on this problem for a couple of days now but I can't seem find the right solutions. I'm hoping that someone can help me.

I want to to calculate the time interval in minutes between customers with remarks as "New Entry" (new customer who comes in to the branch and get his/her queuing number). Meaning the time interval between the first customer and the 2nd customer, 2nd customer and 3rd customer, etc...

Data Sample:

Branch Code	Date	Time	Product	Service ID	Remarks
DVS01	7/1/21	8:00:00 AM	Money	M001	New Entry
DVS01	7/1/21	8:09:21 AM	Money	M001	Serving
DVS01	7/1/21	8:12:00 AM	Money	M001	Done
DVS01	7/1/21	8:10:00 AM	Cargo	C001	New Entry
DVS01	7/1/21	8:10:00 AM	Cargo	C001	Serving
DVS01	7/1/21	8:15:12 AM	Cargo	C001	Done
DVS01	7/1/21	9:00:00 AM	Bills	B001	New Entry
DVS01	7/1/21	9:10:00 AM	Bills	B001	Serving
DVS01	7/1/21	9:12:00 AM	Bills	B001	Done

The results that I want:

Branch Code	Date	Time	Product	Service ID	Remarks	Customer Interval
DVS01	7/1/21	8:00:00 AM	Money	M001	New Entry	0
DVS01	7/1/21	8:09:21 AM	Money	M001	Serving
DVS01	7/1/21	8:12:00 AM	Money	M001	Done
DVS01	7/1/21	8:10:00 AM	Cargo	C001	New Entry	10mins
DVS01	7/1/21	8:10:00 AM	Cargo	C001	Serving
DVS01	7/1/21	8:15:12 AM	Cargo	C001	Done
DVS01	7/1/21	9:00:00 AM	Bills	B001	New Entry	50mins
DVS01	7/1/21	9:10:00 AM	Bills	B001	Serving
DVS01	7/1/21	9:12:00 AM	Bills	B001	Done

To further explain my table above. It means, the 1st customer get his queue number at 8:00AM with remarks as "new entry", then a 2nd customer comes in at 8:10AM, so that's a 10mins interval from the 1st customer. Then a 3rd customer comes in at 9:00AM, so that's a 50mins interval from the 2nd customer.

The remarks "Serving" pertains to the time the customer was served or called and "Done" means transactions was completed. Just in case you want to know.

I tried this solution https://community.powerbi.com/t5/Desktop/Time-difference-between-next-row/m-p/338621, but it doesn't work for me and I got the same problem as posted there.

Any help will be greatly appreciated.

Thank you in advance.

[amitchandak · Accepted solution]

@ljmanayon , Try a new column like

new column =
var _1 = maxx(filter(Table, [Branch] =earlier([Branch]) && [Date] = earlier([Date]) && [Time] <earlier([Time]) && [Remarks] = "New Entry"), [Time])
return
if( [Remarks] = "New Entry" , datediff(_1,[time], minute))

or measure like

new measure =
var _1 = maxx(filter(allselected(Table), [Branch] =max([Branch]) && [Date] = max([Date]) && [Time] <max([Time]) && [Remarks] = "New Entry"), [Time])
return
if( max([Remarks]) = "New Entry" , datediff(_1,max([time]), minute))

[ljmanayon]

Hi Sir @amitchandak ,

Thank you very much for your help. I really do appreciate your help.

Your formula for "new column" I think it works. However, when I tried the measure, it gives me nothing, it's blank.

I temporarily named my column, "Customer Interval" and "Interval" for measure, just a sample name to test the formula.

This ones work for creating a column

Customer Interval =

var Diff =

MAXX(FILTER('QMS Data',[Branch]

=EARLIER([Branch]) && [Date] = EARLIER([Date]) && [Time]

< EARLIER([Time]) && [Remarks] = "New Entry"),[Time])

RETURN

IF([Remarks] = "New Entry",

DATEDIFF(Diff, [Time], MINUTE))

But for the measure it doesn't,

Interval =

var Diff =

MAXX(FILTER(ALLSELECTED('QMS Data'),[Branch]

=MAX([Branch]) && [Date] = MAX([Date]) && [Time]

< MAX([Time]) && [Remarks] = "New Entry"),[Time])

RETURN

IF(MAX('QMS Data'[Remarks]) = "New Entry",

DATEDIFF(Diff, MAX('QMS Data'[Time]), MINUTE))

I tried to use this, but it gives me a syntax error on the column name [Remarks] and [Time] with bold font.

Interval =

var Diff =

MAXX(FILTER(ALLSELECTED('QMS Data'),[Branch]

=MAX([Branch]) && [Date] = MAX([Date]) && [Time]

< MAX([Time]) && [Remarks] = "New Entry"),[Time])

RETURN

IF(MAX([Remarks]) = "New Entry",

DATEDIFF(Diff, MAX([Time]), MINUTE)) 

I'd prefer to use a measure to save some memory since I have hundreds of thousounds of data and counting.

I can't figure out where did I go wrong.

Regards.