Difference Between First Bill, Second Bill & Last Bill

[Jeevan1991]

Dear Friends,

I want a help on below data.

I am having a data like the below one.

Date	Mobile #	Bill #	Bill Time
27-Jan-2021	9743247323	546	16:38:36
27-Jan-2021	9743247323	578	17:48:55
27-Jan-2021	9743247323	588	21:38:36
27-Jan-2021	6456547657	543	13:38:36
27-Jan-2021	6756765886	454	10:38:36
27-Jan-2021	7456575666	746	11:38:36
27-Jan-2021	5645754676	755	17:38:36
27-Jan-2021	5645754676	800	18:38:36
27-Jan-2021	5645754676	876	20:38:36
28-Jan-2021	9743247323	546	17:38:36
28-Jan-2021	9743247323	578	17:38:36
28-Jan-2021	9743247323	588	21:38:36
28-Jan-2021	6456547657	543	15:38:36
28-Jan-2021	6756765886	454	00:38:36
28-Jan-2021	7456575666	746	09:38:36
28-Jan-2021	5645754676	755	17:48:36
28-Jan-2021	5645754676	800	20:38:36
28-Jan-2021	5645754676	876	21:58:36

Now i want to find the bill time difference between first bill time, second bill time and last bill time and so on based on mobile numbers.

Please help me on DAX to find the table like the below one and the result column is "Bill Time Difference".

Date	Mobile #	Bill #	Bill Time	Bill Time Difference
27-Jan-2021	9743247323	546	16:38:36	First Bill
27-Jan-2021	9743247323	578	17:48:55	01:10:19
27-Jan-2021	9743247323	588	21:38:36	05:00:00
27-Jan-2021	6456547657	543	13:38:36	First Bill
27-Jan-2021	6756765886	454	10:38:36	First Bill
27-Jan-2021	7456575666	746	11:38:36	First Bill
27-Jan-2021	5645754676	755	17:38:36	First Bill
27-Jan-2021	5645754676	800	18:38:36	01:00:00
27-Jan-2021	5645754676	876	20:38:36	03:00:00
28-Jan-2021	9743247323	546	17:38:36	First Bill
28-Jan-2021	9743247323	578	17:38:36	00:00:00
28-Jan-2021	9743247323	588	21:38:36	04:00:00
28-Jan-2021	6456547657	543	15:38:36	First Bill
28-Jan-2021	6756765886	454	00:38:36	First Bill
28-Jan-2021	7456575666	746	09:38:36	First Bill
28-Jan-2021	5645754676	755	17:48:36	First Bill
28-Jan-2021	5645754676	800	20:38:36	02:50:00
28-Jan-2021	5645754676	876	21:58:36	04:10:00

@v-easonf-msft @speedramps @amitchandak

[Geradav · Accepted solution]

@Jeevan1991 

If you were to use DAX and a calculated column here below is the DAX you could use:

---

TimeDiff =
VAR mobileNum = [Mobile #]
VAR billDate = [Date]
VAR billTime = [Bill Time]
VAR firstBillTime =
    CALCULATE (
        MIN ( [Bill Time] ),
        FILTER (
            TableBills,
            TableBills[Mobile #] = mobileNum
                && TableBills[Date] = billDate
        )
    )
RETURN
    IF ( firstBillTime = billTime, TIME ( 0, 0, 0 ), billTime - firstBillTime )

---

However, passing by Power Query might be a better approach to add a column

Let us know if that works for you

David

[Jeevan1991]

It is working bro.

Thank you so much for your time and effort.

In case of any additional requirement i will get back to you.

[amitchandak]

@Jeevan1991 , You can create a rank columns like these

rank = rankx(filter(Table, [Mobile #] =earlier([Mobile #])) ,[Bill #],,asc)

date time = [date]+ [time]
time diff as column = [date time] = maxx(filter(Table, [Mobile #] =earlier([Mobile #]) && [date time] <earlier([date time])),[date time])
or
time diff as column = [date time] = maxx(filter(Table, [Mobile #] =earlier([Mobile #]) && [Rank] <earlier([Rank])),[date time])