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

Srinivas_Itech's avatar
Srinivas_Itech
New Member
3 years ago

Hi Sir ,

 

I need differance between close date to log date differance based on early dates .

 

Ex : First Log Date = 01-01-2022 First Close Date = 10-01-2022

      2nd Log Date = 20-01-2022 2nd Close Date = 30-01-2022

      3rd Log date = 10-02-2022  3rd Close Date = 20-02-2022

 

now need differance between "First Close Date" - "2nd Log Date" in 2nd column 

And  "2nd close date" - "3rd close date" in 3rd Column 

 

Kindly help sir 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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

  • Srinivas_Itech's avatar
    Srinivas_Itech
    New Member
    3 years ago

    Awesome Sir thanks  a lot sir