SUM all subjects IF 2 date columns are equal by day, but not by time

[Anonymous]

Hello everyone, 

I'm trying to count how many candidates were never touched in a Vacancy since they applied. I have 2 date/time columns that can tell me that. One is the Candidate Submitted Date (Candidate_SubmittedDate) and the other is Moved to Date (Candidate_CurrentFolder_SetOn). If they match, then the candidate was never touched. 

The way I would do it is like the formula below, but given the fact that these fields contain DATE and TIME they will never match as the system does the 2 processes withing a few seconds apart. Is there a way to compare only the DATES in a Measure? Or I need to go in Power Query and change Type or split columns? 

_Candidate_Never_Moved = SUMX(report, IF([Candidate_SubmittedDate]=[Candidate_CurrentFolder_SetOn], 1, 0))

Do you have any ideas? Thank you very much,

Ovidiu

[vik0810 · Accepted solution]

Hi @Anonymous,

you can use following formula

_Candidate_Never_Moved =
SUMX (
    report,
    IF (
        [Candidate_SubmittedDate].DATE = [Candidate_CurrentFolder_SetOn].DATE,
        1,
        0
    )
)

[vik0810 · Accepted solution]

Hi @Anonymous,

you can use following formula

_Candidate_Never_Moved =
SUMX (
    report,
    IF (
        [Candidate_SubmittedDate].DATE = [Candidate_CurrentFolder_SetOn].DATE,
        1,
        0
    )
)

[Anonymous · Accepted solution]

Found the issue it's [Candidate_SubmittedDate].[Date]

Thank you very much! 

Highly appreciated,

Ovidiu

[Anonymous]

Tried to reverse the fields... They are both DATE/TIME, i don't know why the 2nd appears as grey. 

[Anonymous]

hi @vik0810 

I've tried that, but it gives me an error. Not sure where i make a mistake here: