Forum Discussion

anupambit1797's avatar
anupambit1797
Iron 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 "...
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.

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

 

Resources