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