Multi-Record Calculation Issue

[TCatron18]

Hello-

I have a calculation for obtaining hours from the past 12 months based on the effective date of an action. I am running into an issue when there are multiple actions that have occurred in the dataset where the first value calculates the correct sum of hours; however, the second value is the sum of all hours regardless of date. What am I missing in my calculation to account for these multiple records to calculate appropriately for each instance? 

Past 12 Months Hours =

VAR _EndDate = SELECTEDVALUE( 'PRN Conversion Data'[Effective Date] )

VAR _StartDate = DATEDIFF( _EndDate, -12, MONTH )

RETURN

CALCULATE(

SUM( 'Past 12 Months Hours'[HOURS] ),

FILTER( 'Past 12 Months Hours',

'Past 12 Months Hours'[PER_BEG_DATE] <= _EndDate

&& 'Past 12 Months Hours'[PER_END_DATE] >= _StartDate

)

)

My example employee has two records:

EE ID	Effective Date	Hours
1234	1/7/2024	570.50
1234	10/26/2025	3,524.61

The hours for 1/7/2024 is correct. The hours for 10/26/2025 is incorrect and should be 1,431.54, but instead it's totalling all hours the employee has in the hours table. 

Thanks in advance for any assistance!!

[GeraldGEmerick]

@TCatron18 You are using DATEDIFF incorrectly here. DATEDIFF calculates the difference between two dates, it does not return an actual date value. But, because a date is technically a number, you aren't getting an error. Try using EOMONTH instead of DATEDIFF to return an actual date.

[TCatron18 · Accepted solution]

Thank you @GeraldGEmerick  Looks like using EDATE fixed the issue 🙂