Reply
cursty
Frequent Visitor

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_iditem_idpaid_amountpayment_date
1item-1100May 2, 2024
2item-1500Dec 30, 2023
3item-1800Sep 30, 2023
4item-2200Apr 2, 2023
5item-2100Jan 5, 2021
6item-31500Jun 1, 2022
7item-33000Feb 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

YearNumber of ItemsSum of Expenses
Year 1 (Q2 2023 - Q1 2024)21500
Year 2 (Q2 2022 - Q1 2023)14500
Year 3 (Q2 2021 - Q1 2022)0

0

Total3

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

That is a pretty messed up scenario...

 

lbendlin_0-1727995957916.png

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

That is a pretty messed up scenario...

 

lbendlin_0-1727995957916.png

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)