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 t...
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
     
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanx

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.