Calculating group movements between customisable dates

Adam_Cromie 

To achieve this in Excel, you can use Power Query or formulas. Below are the steps using formulas:

1. **Filter Data by Date Range**: Create a new table or a helper column to filter the data based on the selected date range in the slicer.

2. **Calculate Changes**: Calculate the changes in values for each ID between consecutive dates.

3. **Find Biggest Increases and Decreases**: Sort the calculated changes to find the biggest increases and decreases for each ID.

Here's how you can do it step by step:

### Step 1: Filter Data by Date Range

Let's say you have your original data in columns A to C, and the slicer is based on cells E1 (start date) and F1 (end date). You can use the following formula in a helper column to filter the data:

In cell G2 (assuming headers are in row 1):
```excel
=AND($A2>=E$1,$A2<=F$1)
```

Drag this formula down for all rows of your data. This will give you TRUE for rows that fall within the selected date range in the slicer.

### Step 2: Calculate Changes

In another column, calculate the changes in values for each ID between consecutive dates:

In cell H2:
```excel
=IF(AND(G2,G3),C3-C2,"")
```

Drag this formula down for all rows of your data. This will give you the change in value for each ID between consecutive dates.

### Step 3: Find Biggest Increases and Decreases

Finally, you can use Excel's sorting feature or formulas like LARGE and SMALL to find the biggest increases and decreases:

#### Increase Table
In cell K2 (assuming headers are in row 1):
```excel
=IFERROR(INDEX($B$2:$B$10,MATCH(LARGE(IF($H$2:$H$10>0,$H$2:$H$10),ROW()-1),$H$2:$H$10,0)),"")
```

In cell L2:
```excel
=IFERROR(LARGE(IF($H$2:$H$10>0,$H$2:$H$10),ROW()-1),"")
```

#### Decrease Table
In cell N2 (assuming headers are in row 1):
```excel
=IFERROR(INDEX($B$2:$B$10,MATCH(SMALL(IF($H$2:$H$10<0,$H$2:$H$10),ROW()-1),$H$2:$H$10,0)),"")
```

In cell O2:
```excel
=IFERROR(SMALL(IF($H$2:$H$10<0,$H$2:$H$10),ROW()-1),"")
```

Drag these formulas down to get the top changes based on your data.

These formulas assume that your data is in cells A2:C10, adjust the range according to your actual data range. Also, remember to replace the cell references with your actual cell references if they differ.


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!