Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rlhim
Helper I
Helper I

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

NameIDDate
Rachel110/1/2023
Rachel110/2/2023
Rose210/2/2023
Rose210/3/2023
Matt310/3/2023
Damon5

10/5/2023

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

IDNameBeginEnd
1Rachel10/1/202310/2/2023
3Matt10/3/202310/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 

NameIDDate 
Rachel110/1/2023Certificate
Rachel110/2/2023Certificate
Rose210/2/2023 
Rose210/3/2023 
Matt310/3/2023Certificate
Damon510/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
1 ACCEPTED SOLUTION
jgeddes
Super User
Super 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",
    ""
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
rlhim
Helper I
Helper I

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors