Power BI measure help - Sum for all dates prior and by YYYYMM

[JRM-34]

I have two tables. One has the date the agreeement was made and the other is the date a claim was paid. I need a measure that can create a table where we have all agreement years and the total amount paid with the claims paid for that YYYYMM. Here is an example below.

I have a date table where I linked the date of originiation and the claim paid date to the calendar date table.

I am getting hung up how I can create measure to show the information. I need the YYYYMM and the total of all agreeements by Agreeement Year. I also need the claims paid by YYYYMM but ignore the agreement year.

I have tried numerous measure and I can get them to show on their own but not in the same table.

I know I am probably missing something minor but I just cannot figure it out. 

Thank you for your help. 

Total OS Table
Date of Origination	Agreement Number	Agreement Year	Amount Paid
1/1/2021	12345	2021	100
5/2/2021	23456	2021	500
8/10/2021	34567	2021	600
2/1/2022	78910	2022	100
4/5/2022	98765	2022	800
6/15/2022	25869	2022	900
4/1/2023	12357	2023	900
6/9/2023	25896	2023	700
11/2/2023	26894	2023	300
3/1/2024	12489	2024	500
3/2/2024	15698	2024	300
3/16/2024	10258	2024	200

Claims Table
Claim Date Paid	Agreement Number	Paid Claim Amount
1/1/2024	12345	100
1/2/2024	23456	300
1/3/2024	34567	100
2/14/2024	78910	100
1/4/2024	98765	200
1/5/2024	25869	100
1/6/2024	12357	600
2/5/2024	25896	100
2/10/2024	26894	200
1/7/2024	12489	100
1/8/2024	15698	200
1/9/2024	10258	100
2/6/2024	12345	200
2/7/2024	23456	100
2/8/2024	34567	200
2/11/2024	78910	400
2/12/2024	98765	500
2/13/2024	25869	100
1/10/2024	12357	200
1/11/2024	25896	100
1/12/2024	26894	200
2/9/2024	12489	100

Final Output
YYYYMM	Agreement Year	Total Amount Paid for each YYYYMM by Agreement Year	Claims paid in YYYYMM by Agreement Year
202401	2021	1200	500
202401	2022	1800	900
202401	2023	1900	400
202401	2024	1000	500
202402	2021	1200	100
202402	2022	1800	300
202402	2023	1900	1500
202402	2024	1000	100

[v-tangjie-msft · Accepted solution]

Hi @JRM-34 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column on Claims Table.

YYYYMM = YEAR([Claim Date Paid])*100+MONTH([Claim Date Paid])

(3) We can create a model relationships and a measure.

Claims paid = CALCULATE(SUM('Claims Table'[Paid Claim Amount]),USERELATIONSHIP('Claims Table'[Agreement Number],'Total OS Table'[Agreement Number]))

(4) Then the result is as follows.

If the result is inconsistent with your expected result, please give an example of your calculation logic.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.