Calculating Total Hours/FTE For Most Recent Effective Date

[Violator]

Hi,

Hoping someone can help me with this.  Fairly new to PBI, so be gentle 🙂

I have a Date Table setup using Calendar.

I have a table of Employee Hours/FTE, I want to be able to calculate the total hours or FTE of everyone whose Effective Date falls before a specified date (slicer, using Date field from Date Table).  Essentially a historical view of Hours/FTE.

It must be the most recent entry that meets the criteria, and ideally I'd like it to consider the effective date if the person is also a leaver, but the leave date is not within the criteria.  I need it to be grouped by Month, ideally.

For example, if I set the date to 31/01/2024, I'd expect it to return the following hours:

January : 108 (38+35+35) = row 4+row 5+row 7

If I set the date to 31/01/2022, I'd expect it to return the following hours :

January : 110 (35+35+40) = row 3+row 7+row 10

And if the date was 31/04/2022, I'd expect it to return the following hours :

Jan :  110 = row 3+row 7+row 10
Feb : 110 = row 3+row 7+row 10
Mar : 110 = row 3+row 7+row 10
April  : 70 (35+35) = row 3+row 7

I've tried various MAXX and FILTER combinations in a measure, but nothing really gives me what I'm after.  I've managed to add a column that holds the most recent date, and I've added another column that has a flag of 1 if it's the most recent, but ideally I'd want it in a measure.

Hope I've explained this well enough.  Appreciate any help.

Sample Data (this is Excel, but the PBI Table is the same)

Thanks in advance 🙂

[Violator]

Friendly bump, hope that's OK.

[Anonymous]

Hi, @Violator 

I've read through your requirements and there are still a few that I didn't get, so I hope you can answer them.
If the slicer date is 4/30/2022 (there is no 31st of April), then the date earlier than that is shown in the filter below.

Didn't understand how you got the data you gave.

Best Regards,
Yang
Community Support Team

[Violator]

Hi Yang,
Sorry, you;re right, there's no 31st April 🙂 my mistake.  Use 30th April.

I'm not sure how else to explain it.  I'd like historical hours/FTE data based on the effective date, and whether that person was a leaver or not, i.e. each month should show me a snapshot of the staff hours/FTE for that month.  I've put in about rows being added to show how I get the hours amount, this will change depending on when the effective date is.

So if the end date was 30/04/2022, I should get

Jan : 110 hours =row 3+row 7+row 10
Feb : 110 hours =row 3+row 7+row 10
Mar : 110 hours =row 3+row 7+row 10
April  : 70 hours =row 3+row 7 (row 10 is now excluded as they are now a leaver)

If the end date was 30/04/2024, then I'd expect to get

Jan :  108 hours =row 4+row 5+row 7
Feb : 108 hours =row 4+row 5+row 7
Mar: 108 hours =row 4+row 6+row 8 (row 6 & 8 now instead of 5 & 7 due to effective date)
Apr : 108 hours =row 4+row 6+row 8

I hope that's a bit clearer.  Maybe I don't need the 'end' date, and just say 'to date'.