Forum Discussion

Srinivas_Itech's avatar
Srinivas_Itech
New Member
3 years ago
Solved

Date Difference

Hi All ,   I have a issue iam trying to take date diff from 2 date columns based on 1 value but iam getting error kindly help to resolve the issue  Thank's in advance    EX :  Order ID Lo...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Srinivas_Itech So basically MTBF. If you really need them in the exact order, then you will need an index (second example). PBIX is attached below signature.

    Diff Days = 
    VAR __LogDate = [Log Date]
    VAR __Order = [Order ID]
    VAR __PrevLogDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] < __LogDate),[Log Date])
    VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Log Date] = __PrevLogDate),[Close Date])
RETURN
    DATEDIFF(__PrevDate, __LogDate, DAY)

     

    Diff Days 2 = 
    VAR __LogDate = [Log Date]
    VAR __Index = [Index]
    VAR __Order = [Order ID]
    VAR __Prev = MAXX(FILTER('Table',[Order ID] = __Order && [Index] < __Index),[Index])
    VAR __PrevDate = MAXX(FILTER('Table',[Order ID] = __Order && [Index] = __Prev),[Close Date])
RETURN
    DATEDIFF(__PrevDate, __LogDate, DAY)

    See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous