Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

calculation with dates

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 employeedate startdate endteam
Margret1-1-202324-1-2023A
Margret25-1-202315-2-2023B
Margret16-2-202330-4-2023C
Margret1-5-2023 D
Pierre1-1-202331-5-2023C
Pierre1-6-2023 A
Tom1-1-20235-3-2023C
Tom6-3-202330-4-2023B

 

Table working hours:

Name employeedateworking hours
Magret5-1-20233
Magret8-1-20235
Magret15-1-20236
Magret16-1-20232
Magret25-1-20236
Magret29-1-20234
Magret17-2-20238
Magret18-2-20234
Magret8-3-20238
Magret15-3-20238
Magret16-3-20236
Magret19-3-20234
Magret28-3-20238
Magret17-4-20237
Magret24-4-20233
Magret27-4-20239
Magret26-5-20237
Pierre5-1-20233
Pierre8-1-20235
Pierre15-1-20236
Pierre16-1-20232
Pierre25-1-20236
Pierre29-1-20234
Pierre17-2-20238
Pierre18-2-20234
Pierre8-3-20238
Pierre15-3-20238
Pierre16-3-20236
Pierre19-3-20234
Pierre28-3-20238
Pierre17-4-20237
Pierre24-4-20233
Pierre27-4-20239
Pierre26-5-20237
Tom5-1-20233
Tom8-1-20235
Tom15-1-20236
Tom16-1-20232
Tom25-1-20236
Tom29-1-20234
Tom17-2-20238
Tom18-2-20234
Tom8-3-20238
Tom15-3-20238
Tom16-3-20236
Tom19-3-20234
Tom28-3-20238
Tom17-4-20237
Tom24-4-20233
Tom27-4-20239
Tom26-5-20237
Need Help
  • some_bih's avatar
    some_bih
    3 years ago

    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
     

14 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      some_bih
      Community Champion
      3 years ago

      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
       
      • some_bih's avatar
        some_bih
        Community Champion
        3 years ago

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