calculation with dates

[Anonymous]

Hi, I have a table with employees date joined by team and date left by team. As a result, employees end up in several rows. For example employed by team A from 1-1-2020 to 31-7-2021 and employed by team B from 8-1-2021 to present. Now I also have a table with written hours per day per project. And I have a date table. How can I get a list of the written time in hours per project from each team. The date written from the table hours should look at table employees and look based on the date where in which team they were. How do I do that within Power BI? Do I use Dax or add a calculated column? Some screenshots of the colums below.

Table employee

Name employee	date start	date end	team
Margret	1-1-2023	24-1-2023	A
Margret	25-1-2023	15-2-2023	B
Margret	16-2-2023	30-4-2023	C
Margret	1-5-2023		D
Pierre	1-1-2023	31-5-2023	C
Pierre	1-6-2023		A
Tom	1-1-2023	5-3-2023	C
Tom	6-3-2023	30-4-2023	B

Table working hours:

Name employee	date	working hours
Magret	5-1-2023	3
Magret	8-1-2023	5
Magret	15-1-2023	6
Magret	16-1-2023	2
Magret	25-1-2023	6
Magret	29-1-2023	4
Magret	17-2-2023	8
Magret	18-2-2023	4
Magret	8-3-2023	8
Magret	15-3-2023	8
Magret	16-3-2023	6
Magret	19-3-2023	4
Magret	28-3-2023	8
Magret	17-4-2023	7
Magret	24-4-2023	3
Magret	27-4-2023	9
Magret	26-5-2023	7
Pierre	5-1-2023	3
Pierre	8-1-2023	5
Pierre	15-1-2023	6
Pierre	16-1-2023	2
Pierre	25-1-2023	6
Pierre	29-1-2023	4
Pierre	17-2-2023	8
Pierre	18-2-2023	4
Pierre	8-3-2023	8
Pierre	15-3-2023	8
Pierre	16-3-2023	6
Pierre	19-3-2023	4
Pierre	28-3-2023	8
Pierre	17-4-2023	7
Pierre	24-4-2023	3
Pierre	27-4-2023	9
Pierre	26-5-2023	7
Tom	5-1-2023	3
Tom	8-1-2023	5
Tom	15-1-2023	6
Tom	16-1-2023	2
Tom	25-1-2023	6
Tom	29-1-2023	4
Tom	17-2-2023	8
Tom	18-2-2023	4
Tom	8-3-2023	8
Tom	15-3-2023	8
Tom	16-3-2023	6
Tom	19-3-2023	4
Tom	28-3-2023	8
Tom	17-4-2023	7
Tom	24-4-2023	3
Tom	27-4-2023	9
Tom	26-5-2023	7

[Anonymous]

Hi @some_bih , thank you for you answer. The empty date field means that the employee is still working within team ...
You solution does not work. I get the message that the syntaxis for RETURN is notcorrect. Do you know what goes wrong?

Test =

VAR _startdate=SELECTEDVALUE(formatie[date start])

VAR _enddate=SELECTEDVALUE(formatie[date end])

VAR _employee=SELECTEDVALUE(formatie[Name employee])

VAR _Result=

    CALCULATE(

        [Workin_hours],

        FILTER('working hours',

        'working hours'[date]>=_startdate && 'working hours'[date]>=_enddate && 'working hours'[Name employee]=_employee

    )

RETURN _result

)

If i want the team added to the second table "working hours". How can I do that? 

[some_bih · Accepted solution]

Hi @Anonymous 

for Test measure use version below ) was on wrong place. Adding "working hours" to second table - not clear what you want. You already have it some table?

Test =

VAR _startdate=SELECTEDVALUE(formatie[date start])

VAR _enddate=SELECTEDVALUE(formatie[date end])

VAR _employee=SELECTEDVALUE(formatie[Name employee])

VAR _Result=

    CALCULATE(

        [Workin_hours],

        FILTER('working hours',

        'working hours'[date]>=_startdate && 'working hours'[date]>=_enddate && 'working hours'[Name employee]=_employee

    )

)

RETURN _result

[Anonymous]

Hi @some_bih , I have the DAX formular below. I want in my second table a new column with the team a employee is part of at the date he worked. I think there needs something on the red questionmarks. Or do you have an other solution?

Test =

VAR _startdaterooster=SELECTEDVALUE('table 1'[startdate])

VAR _enddaterooster=SELECTEDVALUE('table 1'[enddate])

VAR _medewerker=SELECTEDVALUE('table 1'[startdate])

VAR _Result=

   CALCULATE(?????

    FILTER('RB-BI_UREN',

        'table 2'[Date]>=_startdaterooster

        && 'table 2'[Date]<=_enddaterooster

        && 'table 2'[Date]=_medewerker)

    )

RETURN _result

[some_bih]

Hi @Anonymous I assume employee belong to some team and there is some table. This table should be connected with relationship and we will see the result. In what table we could find this?

[Anonymous]

Hi som_bih, the problem is the emplyee has several lines because it belognes during time to sevral teams. you get a many to many relationship, els it would be easy. 

To get the right team at time hours worked it should look at the date en then find the right team.

[some_bih]

Hi @Anonymous still, one employee belong to only one team at certain day? Or...

[Anonymous]

Hi some_bih, that is right.

Do you know how I  can add a column to the table below?  

Table working hours:

Name employee	date	working hours
Magret	5-1-2023	3
Magret	8-1-2023	5
Magret	15-1-2023	6
Magret	16-1-2023	2
Magret	25-1-2023	6
Magret	29-1-2023	4
Magret	17-2-2023	8
Magret	18-2-2023	4
Magret	8-3-2023	8
Magret	15-3-2023	8
Magret	16-3-2023	6

There is a many to many relationship between the table "working hours"  and "employee"/

For every line in the new column it need to  look in the table "employee" at which team the employee belongs at a certain day. 

Do you have a suggestion?

[some_bih]

Hi @Anonymous please share file with me so I can see overall model. Describe wanted solution so I can check what I can do.

[Anonymous]

@some_bih , How can I share the pbix file with you?

[some_bih]

@Anonymous try DM(right upper corner)

[Anonymous]

yes, that is right. 

[some_bih]

Hi @Anonymous  accept it as solution so other member could use it. thank you

[Anonymous]

Thanx

[some_bih]

Hi @Anonymous  during testing, I found 2 issues in your sample:

1) blank end date - I inserted 31.12.2023 to have valid test data and appling data on real date
2) Margret in one table & Magret  in second table: I overvrite Margret to be in both tables

3) not data for Pierre 1.6.2023 - blank end data (31.12.2023 after my insert as in 1)) so it is not shown in results 

I created 2 measures as below. Adjust Sheet3 and Sheet1 to your table name. 

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

Workin_hours = SUM(Sheet3[working hours]) 

Test =

VAR _startdate=SELECTEDVALUE(Sheet1[date start])

VAR _enddate=SELECTEDVALUE(Sheet1[date end])

VAR _employee=SELECTEDVALUE(Sheet1[Name employee])

VAR _Result=

    CALCULATE(

        [Workin_hours],

        FILTER(Sheet3,

        Sheet3[Date]>=_startdate && Sheet3[Date]<=_enddate && Sheet3[Name employee]=_employee)

    )

RETURN _Result