Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Returning an output within a time range

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!  

 

Load Time
Time Range
  • Watsky's avatar
    Watsky
    5 years ago

    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.

2 Replies

  • Watsky's avatar
    Watsky
    Solution Sage
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

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