Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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 !!
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |