Calculating Moving Range Column In A Collection

(0) Share

Report

Posted on by PeterDonohue

162

I have a collection with multiple columns. I have a DATE column and a RESULT column.

I need to figure out how to add a column that will calculate the difference between one row and the row before it for the RESULT column. Below I have indicated this as a DIFFERENCE column. I also need them to be absolute values but I think I can figure that out, I'm just having issues figuring out how to compare the 2 rows.

Date	Result	Difference
01/01/2024	10
02/01/2024	12	2
03/01/2024	15	3
04/01/2024	18	3

Thanks in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Nandit 1,568 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi

Here's how you can do it. I have added a row number to reference the previous item.

ClearCollect( testCol, { Date: "01/01/2024", Result: 10 }, { Date: "02/01/2024", Result: 12 }, { Date: "03/01/2024", Result: 15 }, { Date: "04/01/2024", Result: 18 } ); ClearCollect( testCol, ForAll( Sequence(CountRows(testCol)), Patch( Last( FirstN( testCol, Value ) ), {RowNumber: Value} ) ) ); ClearCollect( testCol2, AddColumns( testCol, Difference, If( ThisRecord.RowNumber = 1, 0, ThisRecord.Result - Index( testCol, ThisRecord.RowNumber - 1 ).Result ) ) )

Here's how it looks:

Hope this helps.

Kind regards,

Nandit

If this answers your query, please mark this response as the answer.
If its helpful, please leave a like. Thanks!

Was this reply helpful? Yes No
PeterDonohue 162 on at

Like (0)

Report
Copy link

Link copied!

Hey that worked great. Thanks so much!

Was this reply helpful? Yes No
Nandit 1,568 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi PeterDonohue,

Glad it worked.

What exactly is happening in the following part:

Patch(

  testCol2,

  colAvgmR.ID,

  colAvgmR

);

I didn't get this part. It's giving me the same error. The syntax should be like this:

Patch(testCol2, Item to Patch OR Defaults(testCol2), {column: ValueToPatch})

Not sure how the first part is working for you.

Steps I understand:

1. You are calculating the average

2. Creating a new collection with Record IDs and putting average value against all

3. ...

It would be helpful if you could type the steps here or explain the two patch statements.

Thanks & regards,

Nandit

If this answers your query, please mark this response as the answer.

If its helpful, please leave a like. Thanks!

Was this reply helpful? Yes No
PeterDonohue 162 on at

Like (0)

Report
Copy link

Link copied!

So the code has been working fine up until a few minutes ago, now the line

{RowNumber:Value}

gives an error "The type of this argument 'RowNumber' does not not match the expected type 'ObjNull'. Found type 'Number'

Any thoughts?

Was this reply helpful? Yes No
Nandit 1,568 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi PeterDonohue,

Where are you using this code? one place or multiple places? And how?

Kind regards,

Nandit

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 1 on at

Like (0)

Report
Copy link

Link copied!

// Initialize testCol with data
ClearCollect(
testCol,
[
{ Date: "01/01/2024", Result: 10 },
{ Date: "02/01/2024", Result: 12 },
{ Date: "03/01/2024", Result: 15 },
{ Date: "04/01/2024", Result: 18 }
]
);

// Add RowNumber to testCol
ClearCollect(
testCol,
AddColumns(
testCol,
"RowNumber",
CountRows(
FirstN(testCol, CountRows(testCol))
)
)
);

// Add Difference column to testCol2
ClearCollect(
testCol2,
AddColumns(
testCol,
"Difference",
If(
RowNumber = 1,
0,
Result - LookUp(testCol, RowNumber = ThisRecord.RowNumber - 1).Result
)
)
);

Was this reply helpful? Yes No