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

[Anonymous]

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/2023
Rachel	1	10/2/2023
Rose	2	10/2/2023
Rose	2	10/3/2023
Matt	3	10/3/2023
Damon	5	10/5/2023

The othe table ("Medical certificates") has the duration of the medical certificates received in office

ID	Name	Begin	End
1	Rachel	10/1/2023	10/2/2023
3	Matt	10/3/2023	10/3/2023

I want to create a calculated column that relates the ID and the date so when I create it it looks something like this 

Name	ID	Date
Rachel	1	10/1/2023	Certificate
Rachel	1	10/2/2023	Certificate
Rose	2	10/2/2023
Rose	2	10/3/2023
Matt	3	10/3/2023	Certificate
Damon	5	10/5/2023

The code that I previously had was this one:

Medical_cert =

IF(and(CONTAINS(RELATEDTABLE('Medical certificates'), 'Medical certificates'[ID], 'Absence'[ID]),contains(RELATEDTABLE('Medical certificates'), 'Medical certificates'[End], 'Absence'[Date])),"Certificate", "")

But the results are not reliable

[Anonymous]

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!

[jgeddes · Accepted solution]

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",
    ""
)