Forum Discussion

rogerdea's avatar
rogerdea
Helper IV
4 years ago

DateTime difference to previous date/time

I am trying to calculate the difference in minutes between the date/time values in my table, to end up with a new column called "Diff" like in the example below.   I looked at similar example...
Need Help
Show and Tell
Fowmy's avatar
Fowmy
Super User
4 years ago

rogerdea 

Add the following DAX code in a new calculated column:

Diff in Minutes = 

VAR __CURRTIME = 'Table1'[DateTime]
VAR __LASTTIME = 
    CALCULATE(
        MAX('Table1'[DateTime]),
        'Table1'[DateTime]<__CURRTIME,
        REMOVEFILTERS('Table1')
    )
VAR __RESULT = 
    DATEDIFF( __LASTTIME, __CURRTIME , MINUTE )
RETURN
    __RESULT

 

  • rogerdea's avatar
    rogerdea
    Helper IV
    4 years ago

    Thank you Fowmy 

     

    I've applied that solution to my data but it's giving strange results, and i'm not sure why.  I've pasted the DAX with the actual real table names, have i applied the solution correctly?

    Thanks

     

     

     

     

    • Fowmy's avatar
      Fowmy
      Super User
      4 years ago

      rogerdea 

      Did add a Measure or a Calculated Column ?

      • rogerdea's avatar
        rogerdea
        Helper IV
        4 years ago

        Fowmy 

         

        I figured out why it wasn't working.  I had a filter on the data which the code ignores, so the DAX is actually fine.

         

        Is it possible to remove the REMOVEFILTERS part and have it working so that the dates dynamically change according to what filters are being used?  I tried deleting that part but the calculation broke.