Evaluate if transaction datetime (also the time matters!) sits within datetime interval

[Giorgio2021]

I have three tables:

1) Project_registration: Basically a stand alone table that sets the start and end of a pledge. Important it is not only the date that matters, but the exact time within the day both for the starting and ending date-time.

2) CalendarTable1: A calendar table which basically does not consider specifically (or at least does not break) into time (hour, minute, second). I am unsure if this is causing my problem - the lack of time granularity.

3) Account_records: Transactions which include specific DateTime field (so every tx happens within a specific date and exact time (hour, minute, second).

I have set the relationships (hopefully correctly) and after a lot of attempts managed to "add" the time component to the date so that the formula checks to the level of row if the transaction happens within the project pledge time interval.

Problem: Why are there two specific values that normally would retrieve "No" (because they happen slightly later than 14 (hour) 45 (minute) 0 (second) of April 30th 2021? All the rest seems to work (all the tx label as "Pledge record" if they fall into the project pledge intervals) but these two in red colour... I am getting mad! 

Any suggestion? Or any wiser way to do the whole formula more stable (and obviously accurate)?

Formula built:

=
IF(
    MAXX( Account_records, DATEVALUE( Account_records[DateTime] ) )
        + MAXX(
            Account_records,
            TIME( Account_records[Time (Hour)], Account_records[Time (Minute)], Account_records[Time (Second)] )
        )
        >= MAXX(
            RELATEDTABLE( Project_registration ),
            DATEVALUE( [Date Time Pledge starts] )
        )
            + MAXX(
                RELATEDTABLE( Project_registration ),
                TIME( Project_registration[Time_Hour - Pledge starts], [Time_Minute - Pledge starts], [Time_Second - Pledge starts] )
            )
        && MAXX( Account_records, DATEVALUE( Account_records[DateTime] ) )
            + MAXX(
                Account_records,
                TIME( Account_records[Time (Hour)], Account_records[Time (Minute)], Account_records[Time (Second)] )
            )
            <= MAXX(
                RELATEDTABLE( Project_registration ),
                DATEVALUE( [Date Time Pledge ends] )
            )
                + MAXX(
                    RELATEDTABLE( Project_registration ),
                    TIME( Project_registration[Time_Hour - Pledge ends], Project_registration[Time_Minute - Pledge ends], Project_registration[Time_Second - Pledge ends] )
                ),
    "Pledge record",
    "No"
)

        
         

[v-easonf-msft]

Hi， @Giorgio2021 

Measure1 =
IF (
    A + B >= C_Start + D_Start
        && A + B <= C_end + D_end,
    "Pledge record",
    "No"
)

Sorry,I did not find any problems with your formula.

It is suggested that you split your original measure into multiple measures(A,B,C_Start, D_Start,C_end , D_end), and troubleshoot step by step through the returned results of each measure.

Best Regards,
Community Support Team _ Eason

[Giorgio2021]

Hi,

Thanks I will do that and see if I can make it to work fully accurately.

Kind regards,

Jore