A measure to sum up from two tables with date

[Silvia_Gulu_BXL]

Hello, I am working on a resource planning project, and there are three tables:

1. Task table with "task start date" "end date" and "required man-days"

2. Resource table with "resource start date" "end date" and "staffing rate"

3. Date table with "Workday?"

I want to create two measures:

1. measure one: sum up the "required man-days" of a date

2. measure two: sum up the "available man-days" provided by the "Resource" of a date

Here is a sample of what I want to achieve in the middle table, and the other tables are around it.

The same color indicates where the number is from:

I tried to create something like:

Task_required_man_days (Wrong)= 
VAR MinDate=MIN('Date'[Date])
VAR MaxDate=MAX('Date'[Date])
VAR workday_count=CALCULATE(DISTINCTCOUNT('Date'[Date]),'Date'[Workday?]=1)
RETURN CALCULATE(DIVIDE(MIN(Task[Task required man-days]),workday_count,0),Task[Task start date]<=MaxDate,Task[Task end date]>=MinDate,REMOVEFILTERS('Date'))

But it didn't work.

 I have the PB file in the link: https://drive.google.com/file/d/1TqmQawBtspiaiVBzQA3BGOyq7kdII3AU/view?usp=sharing

Your help is highly appreciated!

[Anonymous · Accepted solution]

Hi @Silvia_Gulu_BXL,

I'd like to suggest you create a new table to extract task and resource names.
Then you can use this table field on matrix column and write a measure formula to use current date and correspond takes id/resource id to search across tables records.

Regards,

Xiaoxin Sheng