Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help with Conditional formula to compare two rows from a third column

Hey everyone, I've been trying to solve the following problem and thought I could start by adding a conditional column that refers to two consecutive rows from the customerID column, but not even tha...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please correct me if I wrongly understood your question.

    please check the below, that is for the Calculated Column.

     

    Battery START Column =
    VAR currentID = Data[CustomerID]
    VAR previousdatetime =
    CALCULATE (
    MAX ( Data[DateTime] ),
    FILTER (
    Data,
    Data[DateTime] < EARLIER ( Data[DateTime] )
    && Data[CustomerID] = currentID
    )
    )
    RETURN
    COALESCE (
    CALCULATE (
    SUM ( Data[BatteryLVL] ),
    FILTER (
    Data,
    Data[DateTime] = previousdatetime
    && Data[CustomerID] = currentID
    )
    ),
    0
    )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, Anonymous 

Please correct me if I wrongly understood your question.

please check the below, that is for the Calculated Column.

 

Battery START Column =
VAR currentID = Data[CustomerID]
VAR previousdatetime =
CALCULATE (
MAX ( Data[DateTime] ),
FILTER (
Data,
Data[DateTime] < EARLIER ( Data[DateTime] )
&& Data[CustomerID] = currentID
)
)
RETURN
COALESCE (
CALCULATE (
SUM ( Data[BatteryLVL] ),
FILTER (
Data,
Data[DateTime] = previousdatetime
&& Data[CustomerID] = currentID
)
),
0
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.