Forum Discussion

TCatron18's avatar
TCatron18
Helper II
7 months ago
Solved

Multi-Record Calculation Issue

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 IDEffective Date

Hours

12341/7/2024570.50
123410/26/20253,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!!

Need Help

2 Replies

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    7 months ago

    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.