Complicated Available Hours in period

[Anonymous]

Hi everyone,

I'm having a really hard time with this issue...

So I have multiple employees and each employee has one or more contracts. These contracts show how many hours a employee has to work each week together with de dates from when en until when the contract is active. (table with this information is available)

I need to calculate the working days between the two dates in that table. However it has to be possible to calculate this too when there is a filter active like only the available hours for March.

I already have the calculation (calculated column) for the working days between the two dates. But when I apply a filter the result will always be the same.

Does anyone know how to fix this to be dynamically?

[v-yuta-msft]

@Anonymous ,

Could you please share some sample data and give the expected result?

Regards,

Jimmy Tao

[Anonymous]

As you can see in the image above, I have a table with contracts. I have to calculate the hours within the selected date that the employee is available. So, the second table shows some extra information how the formula has to look at the data. It makes use of the working days within the period.

The third table is what the formula has to calculate. Because the contract hours are on a weekly basis, the contract hours are divided by 5.  Now the formula can calculate the available contract hours between a certain period.

The dashboard view is how it is going to be visible in the dashboard.

I don't know how I have to create this formula as I've been working on this formula for many hours...

[tex628]

Is it possible for you to provide some sample data from your main table?

[Anonymous]

Hi @tex628 ,

The data from the main table is not relevant as this is just employee information and not necessary for calculting the available hours.

What is important is that one employee can have multiple contracts, but not within the same data range.