Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.


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


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

// 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
Super User
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.


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


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

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 )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.