Taking current status from a table of dated changes

[mattlancs]

Hi all,

I'm trying to write a come up with a measure that'll tell me how many man hours are in the company on any given day. I've got my data in a table which lists any changes to their contracted hours, something like this:

Person	Date	Hours
Dan Aykroyd	01/01/2018	8
Dan Aykroyd	01/04/2020	6.4
Dan Aykroyd	05/10/2020	0
John Belushi	01/09/2019	8
John Belushi	01/04/2020	6.4

For clarity's sake: 8 hours per day equates to a five day working week, 6.4 hours is a four day working week, 0 hours is no longer working.

I've also got a separate simple date table. I think I'm looking for a measure that will sum up the hours column for each person when the date is as high as possible but still less than the date table's date. Ultimately I'd like to be able to plot a graph showing how the number of man-hours changes over time, or to be able to use this for seeing how many hours someone should have worked in a given year. Any hints will be gratefully received!

[amitchandak]

@mattlancs , what is the expected output.

[mattlancs]

Hi there,

I'm looking to have a measure to give the total contracted hours on any given day in my date table, basically. So I could then use that as an effective company headcount for any day, or to see how many hours should have worked in a date range.

[amitchandak]

@mattlancs , Check if these two measures can help

Total person days = countx(summarize(filter(table, table[Hours]>0), table[date], table[person]),[person])
Total hours = countx(summarize(filter(table, table[Hours]>0), table[date], table[person]),[Hours])

[mattlancs]

Hi there,

Thanks very much for those measures, the effort is much appreciated. Unfortunately they didn't help, as they were still including the outdated earlier entries in the table - I think that's my fault for not explaining the data structure properly. I've come up with a bit of a workaround now in Power Query to change the single date field into both a Start Date and an End Date column, then I can report on that based on the date table being between them. It'll need a bit more work to get the filtering tidy, but that's a job for next week.

Thanks again for your time.