Forum Discussion
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/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
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", "" )
2 Replies
jgeddesSuper User
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", "" )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!