Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX measure for comparing rows and time difference

Hi, I'm trying to build a measure (called Time Diff) that compares two consecutive rows, in this case the rows in the Time column. As shown in the column on the far right, I want the output to be in...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Anonymous create this measure:

     

    Time Diff = 
VAR _current_time = SELECTEDVALUE('Table'[Time])
VAR _previous_time = 
    MAXX(
        FILTER(
            ALL('Table'),
            'Table'[Time] < _current_time
        ),
        'Table'[Time]
    )
VAR _result = (_current_time - _previous_time) * 86400
RETURN
    IF(
        NOT ISBLANK(_previous_time), 
        _result
    )

     





          

    Showcase Report – Contoso By SpartaBI

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for your answer! Almost there... 🙂 _previous_time returns _current_time - 1 sec, i.e. the previous timestamp. For example _previous_time for Time 07:06:13 becomes 07:06:12.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Solved it! Just replaced the ALL function with ALLSELECTED. Thanks for your help SpartaBI ğŸ™‚