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

      • some_bih's avatar
        some_bih
        Community Champion
        3 years ago

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