- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Matrix - Group By date range and calculate the SUM for given item IDs
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-20-2024 07:27 AM | |||
10-02-2023 11:09 AM | |||
10-23-2023 09:55 PM | |||
07-23-2024 06:34 AM | |||
06-02-2024 07:33 AM |
User | Count |
---|---|
86 | |
82 | |
42 | |
40 | |
35 |