Returning an output within a time range

[Anonymous]

Hi, please help to write an appropriate DAX code.

The issue:

A transaction is a row that contains a Driver Number, Vehicle Number, Load Date and a Load Time.

Upon checking the fact table, if a transaction for the same driver number, same load date, and load time in the time range of an hour (before or after the current load time) is found, then return the previous vehicle number, else return blank.

Thanks!  

[Watsky · Accepted solution]

Hey @Anonymous , how about this:

Output = 
VAR CALC =
    CALCULATE (
        MAX ( 'Table'[Load Time] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Load Date], 'Table'[Driver Number] ),
            EARLIER ( 'Table'[Load Time] ) - 'Table'[Load Time]
        )
    )
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            'Table',
            'Table'[Vehicle Number] < EARLIER ( 'Table'[Vehicle Number] )
        ),
        'Table'[Vehicle Number], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, 'Table'[Vehicle Number] )
RETURN
    IF (
        ISBLANK ( CALC ),
        "BLANK",
        IF (
            AND (
                DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) < 60,
                DATEDIFF ( 'Table'[Load Time], CALC, MINUTE ) > -60
            ),
            PreviousValue,
            "BLANK"
        )
    )

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[Anonymous]

Hey @Watsky, Genius! Thank you, it worked as I needed it to.