Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |