Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create a calculated column based on if the date is between two dates

I thought I had solved this problem but it turns out that the data is wrong. I have two tables One has the amount of absence that each employee has ("Absence") Name ID Date Rachel 1 10/1...
General Comment
Need Help
Tips and Tricks
  • jgeddes's avatar
    jgeddes
    2 years ago

    Something like the following might work for you as a calculated column in your abscence table.

    Medical_Cert = 
var _id = 
[ID]
var _date = 
[Date]
var _value = 
COUNTROWS(
    FILTER(certificateTable, certificateTable[ID] = _id && certificateTable[Begin] <= _date && certificateTable[End] >= _date)
)
RETURN
IF(
    _value = 1,
    "Certificate",
    ""
)
Anonymous's avatar
Anonymous
Not applicable
2 years ago

That works excellently but for some reason I had to substract a day in 

certificateTable[Begin]

for it to be correct

 

 

Medical_Cert = 
var _id = 
[ID]
var _date = 
[Date]
var _value = 
COUNTROWS(
    FILTER(certificateTable, certificateTable[ID] = _id && certificateTable[Begin]-1 <= _date && certificateTable[End] >= _date)
)
RETURN
IF(
    _value = 1,
    "Certificate",
    ""
)

 

Thank you very much!