Need help with arcade expression in Dashboard

[KelvinNganga]

I have a layer with monthly offertory from 2020 to 2025 stored in rows. Additionally, I have a summary table with the total offertory for each year.

I want to create an ArcGIS Dashboard that:

1. Displays the total sum of offertory on an Indicator widget.
2. Allows users to filter by year using a Category Selector.
3. When a year is selected, it compares the total offertory for the selected year with the previous year and displays the growth or reduction in a bottom text, showing whether it’s positive or negative growth

Below is one I came up with, but the bottom text is using the sum offertory to subtract from the selected year instead of using the previous year. eg when I click 2024 on the category selector I want the indicator to show the sum offertory of year 2024 and the +ve or -ve change from the previous year

// Calculate the difference between the current and previous year

var currentYearTotal = $datapoint["SUM_Yearly_Offertory"];

var previousYearTotal = $reference["SUM_Yearly_Offertory"];

var difference = currentYearTotal - previousYearTotal;

// Calculate absolute difference and percent change

var absoluteDifference = Abs(difference);

var percentChange = IIf(previousYearTotal == 0, 0, (difference / previousYearTotal) * 100);

// Set default text color and symbol

var bottomTextColor = "black";

var bottomTextSymbol = "";

// Determine if the change is positive or negative

if (difference > 0) {

  bottomTextColor = "green";  // Positive growth

  bottomTextSymbol = "▲";

} else if (difference < 0) {

  bottomTextColor = "red";  // Negative growth

  bottomTextSymbol = "▼";

}

// Return formatted text for the Indicator widget

return {

  middleText: "Ksh " + Text(currentYearTotal, "#,###"),  // Display current year total

  middleTextColor: "black",

  middleTextMaxSize: "large",

  bottomText: bottomTextSymbol + " " + Text(absoluteDifference, "#,###") +

              " (" + Round(percentChange, 2) + "% Change)",

  bottomTextColor: bottomTextColor,

  bottomTextMaxSize: "medium"

};