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

[Anonymous]

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

[Anonymous]

// 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
    Result

This should be the fastest version out of the ones in this thread as it does not use CALULATE.

[Jihwan_Kim · Accepted solution]

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.

[Anonymous]

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 )