Calculating group movements between customisable dates

[Adam_Cromie]

Hi team,
I've got a fairly simple data set, example below:

Date	ID	Value
1/01/2024	1	100
1/01/2024	2	100
1/01/2024	3	100
2/01/2024	1	150
2/01/2024	2	80
2/01/2024	3	200
3/01/2024	1	120
3/01/2024	2	90
3/01/2024	3	150

In my report there's a slicer that adjusts the start date and end date for the page.
I need to create a table, which shows the biggest increases and another with biggest decreases in VALUE, by ID between the DATE's selected in the slicer.

So, when the date range is between 2/1/2024 and 3/1/2024, the desired tables would look like this:
Increase table

ID	Change
2	10

Decrease table

ID	Change
3	-50
1	-30

Thanks in advance, 
Adam.

[johnbasha33]

@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 !!

[Adam_Cromie]

Sorry John, I was trying to do this in Power BI (apologies I didn't stipulate in the original post), not excel. 
Are you able to assist?
Adam.