Using Measures for Future Dates in a table

[Shawkins566]

Hi,

I'm trying to create a resourcing dashbord.  I have a list in a sheet of all bookings of staff onto projects it also includes all the necessary information such as total working hours for that person as well as the number of hours assigned to that project and the start date and end date of the project.

I created a measure to show the current availability for all staff (which was just Max Available Hours - SUM(Hours)) and in my table I just have list of Names and the Measure beside it.  

I want to figure out how to look at availability in the future, I first tried changing the end date but that removed a number of people from the list which I don't want to do, I want all the Names to stay and just the available hours be updated.  I think using End Date is the wrong way to go about it, but I can't seem to figure out the best way.

Any ideas?

Thanks

S

[v-yangliu-msft]

Hi  @Shawkins566 ,

I created some data:

You might consider creating a 24-hour interval date table that has no join relationship with the main table, and using measure to display the value

Here are the steps you can follow：

1. Create calculated table.

Date = GENERATESERIES (DATE (2024, 01, 01), NOW(), 0.041666667)

2. Create measure.

availability =
var _select=
SELECTEDVALUE('Date'[Value])
var _DATEDIFF=
DATEDIFF(
  _select,MAX('Table'[End Time]),HOUR)
return
IF(
    MAX('Table'[End Time])<=_select && MAX('Table'[total working hours]) <= MAX('Table'[assigned time]),MAX('Table'[assigned time]) - MAX('Table'[total working hours]),BLANK())

availability hours =
SUMX(
    ALLSELECTED('Table'),[availability])

3. Result:

If my explanations don't meet your expected results or don't understand your question, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Shawkins566]

I don't think this is quite what I'm after.  I'll share some sample data and try and describe what I'm looking for a bit better.  I'll share once I've created some sample data.  

Many thanks

Sarah