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 that I could do in DAX.
Power Query made my BI stop working, so I would need some way simpler to achieve this operation which would be very straightforward in Excel.
No sum applying a filter with previousday or earlier worked so far, so if you have a solution for this, I would be very grateful.
Cheers
CustomerID Date BatterySTART BatteryLVL
4206463200 25/09/2020 07:30 0 2.23
4206463200 25/09/2020 08:00 2.23 4.55
4206463200 25/09/2020 08:30 4.55 3.86
4201223786 25/09/2020 07:30 0 1.21
4201223786 25/09/2020 08:00 1.21 0.98
4201223786 25/09/2020 08:30 0.98 1.67
4382010030 25/09/2020 07:30 0 1.97
4382010030 25/09/2020 08:00 1.97 2.56
4382010030 25/09/2020 08:30 2.56 3.96
Desired formula for 'BatterySTART' column >>>> C2=IF(A2=A1,D1,0) or not in an Excel format:
'BatterySTART'=if CustomerID equals to previous(CustomerID) then 'BatterySTART = BatteryLVL from previous row otherwise equals 0
Also, 'BatterySTART' column first row must be zero
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.
3 Replies
Hello Anonymous ,
First, create an Index column in Power Query using this link.
Create a calculated column using the below Dax.
Battery Start =
VAR Customer = Battery[CustomerID]
VAR CurrentIndex = Battery[Index]
VAR BatteryStart =
CALCULATE (
MAX ( Battery[Battery Level] ),
FILTER (
Battery,
Battery[CustomerID] = Customer
&& Battery[Index] = CurrentIndex - 1
)
)
RETURN
IF ( Customer = Customer && CurrentIndex = 1, 0, BatteryStart )- Jihwan_Kim
Super User
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.