Sum of Column Based on Date of Another Column

[TCatron18]

I am needing to pull the sum of hours for an ID into another table for the date ranges that are within the past 12 months of the effective date. 

I've tried several different DAX formulas without any luck.

I have two tables:

Hours Table

ID	HOURS	BEGIN	END
1	74.27	4/20/2025	5/3/2025
1	53.67	4/6/2025	4/19/2025
1	83.41	3/23/2025	4/5/2025
1	75.51	3/9/2025	3/22/2025
1	43.93	2/23/2025	3/8/2025
1	33.98	2/9/2025	2/22/2025
1	69.66	1/26/2025	2/8/2025
1	87.48	1/12/2025	1/25/2025
1	72.68	12/29/2024	1/11/2025
1	40.87	12/15/2024	12/28/2024
1	61.47	12/1/2024	12/14/2024
1	57.19	11/17/2024	11/30/2024
1	91.9	11/3/2024	11/16/2024
1	87.12	10/20/2024	11/2/2024
1	66.25	10/6/2024	10/19/2024
1	96.5	9/22/2024	10/5/2024
1	56.25	9/8/2024	9/21/2024

Data Table

ID	EFFECTIVE
1	4/6/2025

I am needing to pull the sum of the hours from the Hours table into the Data table for hours that were within the past 12 months from the effective date in the Data table. 

My data is much larger than provided in the example where the dates in both tables are different for different IDs. 

My expected result:

ID	EFFECTIVE	HOURS
1	4/6/2025	1024.2

Any assistance in getting this achieved would be greatly appreciated.

Thanks!!

[Deku · Accepted solution]

Assuming relationship between id in each table

Measure =

Var endDt = selectedvalue( data[effective date])

Var dartDt = datediff( endDt, -12, month )

Return

Sumx(

Filter(

Hours,

hours[begin] <= endDt &&

Hours[end] >= startDt

),

Hours[hours]

)

[TCatron18]

@Deku I've now run into an error with this setup due to the relationship between the tables where the Data table now has duplicate IDs, so I'm getting an error that there cannot be duplicates in a table where it's on the one side of a many-to-one relationship or used as the key between tables. Do I have any options to resolve this?

[Deku]

You can have a many many relationship and set the filter direction as you previous had

[TCatron18]

@Deku Is the many-to-many relationship something that is in the newer version of PBI? I don't seem to have that option with the May 2021 version. I only have many-to-one, one-to-one, or one-to-many. 

[Deku]

You can use a bridge table like here https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and...

[TCatron18]

@Deku Thank you 😁 That works perfectly!