Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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.
@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])
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |