Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
Tips and Tricks
  • Geradav's avatar
    Geradav
    5 years ago

    Anonymous 

     

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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])

     

  • Geradav's avatar
    Geradav
    Responsive Resident
    5 years ago

    Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.