Hello everyone,
I have pretty simple input file, which contains the details of expenses.
It has the unique id, item id (which can appear multiple times in case few payments have been made for the same item), paid amount (amount paid for specific item on a given date) and payment date
| unique_id | item_id | paid_amount | payment_date |
| 1 | item-1 | 100 | May 2, 2024 |
| 2 | item-1 | 500 | Dec 30, 2023 |
| 3 | item-1 | 800 | Sep 30, 2023 |
| 4 | item-2 | 200 | Apr 2, 2023 |
| 5 | item-2 | 100 | Jan 5, 2021 |
| 6 | item-3 | 1500 | Jun 1, 2022 |
| 7 | item-3 | 3000 | Feb 1, 2022 |
I would like to build a matrix which would show the amount of expenses made in the last 3 years, based on the quarter selected by the user.
For example, if user selects Q1 2024 in the slicer, I would like to show the summary of expenses made from Q2 2021 to Q1 2024.
Unfortunately, it's not that easy as I need to report the expenses based on the last payment date made in that 3 years range and additionally group them by year.
Ideally, the matrix would look like this
| Year | Number of Items | Sum of Expenses |
| Year 1 (Q2 2023 - Q1 2024) | 2 | 1500 |
| Year 2 (Q2 2022 - Q1 2023) | 1 | 4500 |
| Year 3 (Q2 2021 - Q1 2022) | 0 | 0 |
| Total | 3 | 6000 |
Year 1 would show 2 items for the total amount of 1,500: item-1, amount of 1,300 (the last payment for 100 has been made after selected period and it's out of scope) and item-2, amount of 200 (as the first payment has been made before Q2 2021 so it's out of 3 years range)
Year 2 would show 1 item: item-3, total amount of 4,500 (it would appear there as the last payment has been made on Jun 2022)
Year 3 would show nothing, as item-3 has been already reported under Year 2
I would appreciate any help in this matter
Many thanks in advance
