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. Date Mobile # Bill # Bill Time 27-Jan-2021 9743247323 546 16:38:36 27-Jan-2021 9743247323 578 ...
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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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])