Forum Discussion
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...
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.
// With the disclaimer that it should be
// done in Power Query, not in DAX.
[Battery Start] =
var CustID = T[CustomerID]
var Dt = T[Date]
var PriorDate =
topn(1,
filter(
T,
T[Customer] = CustID
&&
T[Date] < Dt
),
T[Date],
ASC
)
var Result =
maxx(
PriorDate,
PriorDate[BatteryLVL]
) + 0
return
ResultThis should be the fastest version out of the ones in this thread as it does not use CALULATE.