Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeevan1991
Helper III
Helper III

Difference Between First Bill, Second Bill & Last Bill

Dear Friends,

 

I want a help on below data.

I am having a data like the below one.

DateMobile #Bill #Bill Time
27-Jan-2021974324732354616:38:36
27-Jan-2021974324732357817:48:55
27-Jan-2021974324732358821:38:36
27-Jan-2021645654765754313:38:36
27-Jan-2021675676588645410:38:36
27-Jan-2021745657566674611:38:36
27-Jan-2021564575467675517:38:36
27-Jan-2021564575467680018:38:36
27-Jan-2021564575467687620:38:36
28-Jan-2021974324732354617:38:36
28-Jan-2021974324732357817:38:36
28-Jan-2021974324732358821:38:36
28-Jan-2021645654765754315:38:36
28-Jan-2021675676588645400:38:36
28-Jan-2021745657566674609:38:36
28-Jan-2021564575467675517:48:36
28-Jan-2021564575467680020:38:36
28-Jan-2021564575467687621: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".

 

DateMobile #Bill #Bill TimeBill Time Difference
27-Jan-2021974324732354616:38:36First Bill
27-Jan-2021974324732357817:48:5501:10:19
27-Jan-2021974324732358821:38:3605:00:00
27-Jan-2021645654765754313:38:36First Bill
27-Jan-2021675676588645410:38:36First Bill
27-Jan-2021745657566674611:38:36First Bill
27-Jan-2021564575467675517:38:36First Bill
27-Jan-2021564575467680018:38:3601:00:00
27-Jan-2021564575467687620:38:3603:00:00
28-Jan-2021974324732354617:38:36First Bill
28-Jan-2021974324732357817:38:3600:00:00
28-Jan-2021974324732358821:38:3604:00:00
28-Jan-2021645654765754315:38:36First Bill
28-Jan-2021675676588645400:38:36First Bill
28-Jan-2021745657566674609:38:36First Bill
28-Jan-2021564575467675517:48:36First Bill
28-Jan-2021564575467680020:38:3602:50:00
28-Jan-2021564575467687621:58:3604:10:00

@v-easonf-msft @speedramps @amitchandak

1 ACCEPTED SOLUTION
Geradav
Responsive Resident
Responsive Resident

@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 = billTimeTIME ( 000 )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

View solution in original post

3 REPLIES 3
Geradav
Responsive Resident
Responsive Resident

@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 = billTimeTIME ( 000 )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.

amitchandak
Super User
Super User

@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])

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.