Forum Discussion
really need help pls with looking at table before
I got the following code
Arriva FLEX instroom =
var test =if(DATEDIFF(RELATED('Dim medewerker'[Indienstdatum]), RELATED('Dim medewerker'[Uitdienstdatum]), MONTH) = 0 && RELATED('Dim medewerker'[Reden verlaten baan]) = BLANK(), 0,1)
return
IF (
'Fct medewerker logboek'[Aantal fte] = 0 && RELATED('Dim medewerker'[Indienstdatum]) = 'Fct medewerker logboek'[Datum logboek],
VAR CurrentEmpSince = RELATED('Dim medewerker'[Indienstdatum])
VAR CurrentOutSince = RELATED('Dim medewerker'[Uitdienstdatum])
VAR CurrentEmpTable =
FILTER (
CALCULATETABLE ( 'Fct medewerker logboek', ALLEXCEPT ( 'Fct medewerker logboek', 'Fct medewerker logboek'[Medewerker nummer]) ),
'Fct medewerker logboek'[Aantal fte] = 0
)
VAR TableBefore = FILTER ( CurrentEmpTable, RELATED('Dim medewerker'[Indienstdatum]) < CurrentEmpSince )
VAR PreviousOutSince = MAXX ( TableBefore, RELATED('Dim medewerker'[Uitdienstdatum]))
RETURN
IF(test = 1,
IF (
INT ( CurrentEmpSince - PreviousOutSince ) > 3 || PreviousOutSince == BLANK(),
1
)
)) it calculates when someone entering the company, but on some conditions, one condition is that this code only calculates people entering the company with a zero hour contract and that it only can enter when it is his/her first 0 zero hour contract or if their last contract was more than 3 days before their new contract, see above
but I need help cuz I try to calculate this same calculation but without the given "location" is "Leiden Bijhuur" or "Lisse Bijhuur". I will show you example from what this code will do now and what I want it to do:
current code example:
| Employee | Employee number | entering date | leaving date | reason leaving | hours_contract(fte) | location | Current code result(name: entering) |
| Peter | 1 | 1-1-2019 | 30-06-2019 | - | 0.9 | Leiden | |
| Peter | 1 | 1-7-2019 | 31-7-2019 | - | 0 | Leiden Bijhuur | 1 |
| Peter | 1 | 1-8-2019 | 31-12-2019 | - | 0 | Leiden |
another example
| Employee | Employee number | entering date | leaving date | reason leaving | hours_contract(fte) | location | Current code result(name: entering) |
| Danny | 2 | 2-2-2020 | 2-7-2020 | - | 0 | Lisse | 1 |
| Danny | 2 | 18-8-2020 | 18-3-2021 | - | 0 | Lisse | 1 |
| Danny | 2 | 19-3-2021 | 31-4-2021 | - | 0 | Lisse Bijhuur | |
| Danny | 2 | 1-5-2021 | - | 0 | Lisse |
|
So the problem is that it sees lisse bijhuur en leiden bijhuur as a row and I dont want it to see it as a row
the following example should be the end result
| Employee | Employee number | entering date | leaving date | reason leaving | hours_contract(fte) | location | Current code result(name: entering) |
| Peter | 1 | 1-1-2019 | 30-06-2019 | - | 0.9 | Leiden | |
| Peter | 1 | 1-7-2019 | 31-7-2019 | - | 0 | Leiden Bijhuur | |
| Peter | 1 | 1-8-2019 | 31-12-2019 | - | 0 | Leiden | 1 |
and
| Employee | Employee number | entering date | leaving date | reason leaving | hours_contract(fte) | location | Current code result(name: entering) |
| Danny | 2 | 2-2-2020 | 2-7-2020 | - | 0 | Lisse | 1 |
| Danny | 2 | 18-8-2020 | 18-3-2021 | - | 0 | Lisse | 1 |
| Danny | 2 | 19-3-2021 | 31-4-2021 | - | 0 | Lisse Bijhuur | |
| Danny | 2 | 1-5-2021 | - | 0 | Lisse | 1 |
the question is how can I make this code work that it doesnt see locations as "lisse bijhuur" and "leiden bijhuur"
someone already helped me with this code, I made little changes, but pls someone help me cuz it is really difficult and I cant get it work like how I want it