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
sivarajan21
Post Prodigy
Post Prodigy

Power query logic to create a running total based on few conditions

Hi Team,

 

I have below 2 tables: 

Door and Store Status

sivarajan21_0-1740730894700.png

 

 

I want to create a power query logic for below conditions:

1)Door Status is 'Open' and Store Status is 'C' and current devicedid matches with previous deviceid then find the difference (Door Createdon column) between current row and next row. for example,look at the below screenshot, as the above conditions match it finds the difference from there  

sivarajan21_1-1740730894855.png

 

2)when Door status is closed or Store Status is not closed or current deviceid not matching with previous deviceid then 0 else add current row to the above condition difference.

 

I have put the above conditions based on excel but in power bi, think we can achieve difference between current row and next row of Createdon and adding them in a single column along with conditions like  (Door Status is 'Open' and Store Status is 'C' and current devicedid matches with previous deviceid,when Door status is closed or Store Status is not closed or current deviceid not matching with previous deviceid).

 

In summary, when Door Status = 'Closed' or Store Status <> 'C' or Current deviceid <> Previous Deviceid then 0, if Door Status = 'Open' && Store Status = 'C' && Current deviceid = Previous Deviceid then sum those minutes from thereon until this conditions break to reset to 0.

 

PFA file here B&M (4).pbix
Link to Store status excel file Store Status.csv

Please let me know if you need further details and can't access power bi tables.

I can give access to those

 

Many thanks in advance!

@tharunkumarRTK 

1 ACCEPTED SOLUTION
BeaBF
Super User
Super User

@sivarajan21 Hi! I opened the pbix, but i can't access the overflow data source to see door data. However, try with something like:

 

let
// Step 1: Load your table
Source = YourTable,

// Step 2: Sort by DeviceID and CreatedOn
SortedTable = Table.Sort(Source, {{"DeviceID", Order.Ascending}, {"CreatedOn", Order.Ascending}}),

// Step 3: Add Index Column for Previous Row Reference
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),

// Step 4: Add Previous Row Columns (DeviceID, CreatedOn, and Store Status)
MergedTable = Table.NestedJoin(IndexedTable, "Index", IndexedTable, "Index", "PreviousRow", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"DeviceID", "CreatedOn", "StoreStatus", "DoorStatus"}),

// Step 5: Calculate Time Difference in Minutes
AddedDuration = Table.AddColumn(ExpandedTable, "TimeDifference", each
if [DoorStatus] = "Open" and [StoreStatus] = "C" and [DeviceID] = [DeviceID.1] then
Duration.TotalMinutes([CreatedOn] - [CreatedOn.1])
else
0,
type number
),

// Step 6: Running Sum Until Reset (Grouping by DeviceID)
RunningSum = Table.Group(AddedDuration, {"DeviceID"}, {
{"AllData", each
let
Rows = _,
RunningSumColumn = List.Accumulate(Rows[TimeDifference], {0}, (state, current) =>
if current = 0 then {0} & state else {current + List.First(state)} & List.RemoveFirstN(state, 1)
)
in
Table.FromColumns(Table.ToColumns(Rows) & {List.Reverse(RunningSumColumn)}, Table.ColumnNames(Rows) & {"RunningSum"})
}
}),

// Expand the grouped table
ExpandedFinalTable = Table.ExpandTableColumn(RunningSum, "AllData")

in
ExpandedFinalTable

 

let me know the output and what do you need to fix it.

 

BBF

View solution in original post

2 REPLIES 2
BeaBF
Super User
Super User

@sivarajan21 Hi! I opened the pbix, but i can't access the overflow data source to see door data. However, try with something like:

 

let
// Step 1: Load your table
Source = YourTable,

// Step 2: Sort by DeviceID and CreatedOn
SortedTable = Table.Sort(Source, {{"DeviceID", Order.Ascending}, {"CreatedOn", Order.Ascending}}),

// Step 3: Add Index Column for Previous Row Reference
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),

// Step 4: Add Previous Row Columns (DeviceID, CreatedOn, and Store Status)
MergedTable = Table.NestedJoin(IndexedTable, "Index", IndexedTable, "Index", "PreviousRow", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"DeviceID", "CreatedOn", "StoreStatus", "DoorStatus"}),

// Step 5: Calculate Time Difference in Minutes
AddedDuration = Table.AddColumn(ExpandedTable, "TimeDifference", each
if [DoorStatus] = "Open" and [StoreStatus] = "C" and [DeviceID] = [DeviceID.1] then
Duration.TotalMinutes([CreatedOn] - [CreatedOn.1])
else
0,
type number
),

// Step 6: Running Sum Until Reset (Grouping by DeviceID)
RunningSum = Table.Group(AddedDuration, {"DeviceID"}, {
{"AllData", each
let
Rows = _,
RunningSumColumn = List.Accumulate(Rows[TimeDifference], {0}, (state, current) =>
if current = 0 then {0} & state else {current + List.First(state)} & List.RemoveFirstN(state, 1)
)
in
Table.FromColumns(Table.ToColumns(Rows) & {List.Reverse(RunningSumColumn)}, Table.ColumnNames(Rows) & {"RunningSum"})
}
}),

// Expand the grouped table
ExpandedFinalTable = Table.ExpandTableColumn(RunningSum, "AllData")

in
ExpandedFinalTable

 

let me know the output and what do you need to fix it.

 

BBF

Hi @BeaBF ,

 

Many thanks for your response!

I resolved this!

 

Thanks in advance!

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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors