Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Jan 31, 2025
Solved

Calculate the time difference with a specific criteria

Dear Experts, 

                    I have a data like below in the Worksheet "Main-Page_Data" :-

Column "A" -> can have only 2 values 

Inactive->Connected 

OR


Connected->Inactive

Column "C" has different values for ueIdCu, each ueIdCU can go from "Inactive->Connected" OR
"Connected->Inactive"

In Column "F", I need to have the time-diff for how long the UE took to go from Connected to Inactive state(which is calculated by :-

Need to populate Column"F" for each transition for each ueIdCu and then get the data like in Column H-I-J as below for each ueIdCu

Thanks in Advance,

Br,

Anupam

excel
Formulas and Functions
power bi
  • m_tarler's avatar
    m_tarler
    Jan 31, 2025

    Here is an option that I think will work in legacy versions but not sure and may require ctrl-shift-enter:

    =IFERROR([@Time]-1/(1/SUMPRODUCT(MAX([Time]*([Time]<[@Time])*([ueIdCu]=[@ueIdCu])*([Starting RRC transition procedure]<>[@[Starting RRC transition procedure]])))),"")

    NOTE: this version does not provide that added warning when the prior entry is the same procedure as the current.

6 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 31, 2025

    While the is not necessarily what I would recommend, it is what I would use if the problem were mine.

    Worksheet formula
= LET(
    ueIdCu, UNIQUE(Table1[ueIdCu]),
    EVALTHUNKARRλ(MAP(ueIdCu, TIMEDIFFλ))
  )

    where

    TIMEDIFFλ
= LAMBDA(selected,
    LET(
        filtered,   FILTER(HSTACK(Table1[Starting RRC transition procedure], Table1[Time]), Table1[ueIdCu]=selected),
        transition, TAKE(filtered,,-1),
        state,      DROP(TEXTAFTER(TAKE(filtered,,1), "->"),-1),
        timediffs,  DROP(transition, 1) - DROP(transition,-1),
        LAMBDA(
            VSTACK(
                HSTACK(selected, "Time-diff"),
                HSTACK(state, timediffs)
            )
        )
    )
)

     

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 31, 2025

    Here is a formula you can use that will be robust enough to address multiple potential errors and changing the sorting.  Now I did format the table as a table (Home -> Format as Table).

    =LET(prior,MAXIFS([Time],[ueIdCu],[@ueIdCu],[Time],"<"&[@Time]),
     out, IF(IFERROR(prior=0,0),"",
          IF(XLOOKUP([@ueIdCu]&prior,[ueIdCu]&[Time],[Starting RRC transition procedure])=[@[Starting RRC transition procedure]],
             "Error - Last entry was same transition", 
             [@Time]-prior)),
     out)

     

    • anupambit1797's avatar
      anupambit1797
      Steel Contributor
      Jan 31, 2025

      Thanks m_tarler , may I ask to provide a solution with normal legacy functions? LET is really powerful function and I am still digesting it..  or any PQ with the UI steps?

       

      Thanks & Regards

      Anupam Shrivastava

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Jan 31, 2025

        Here is an option that I think will work in legacy versions but not sure and may require ctrl-shift-enter:

        =IFERROR([@Time]-1/(1/SUMPRODUCT(MAX([Time]*([Time]<[@Time])*([ueIdCu]=[@ueIdCu])*([Starting RRC transition procedure]<>[@[Starting RRC transition procedure]])))),"")

        NOTE: this version does not provide that added warning when the prior entry is the same procedure as the current.

Resources