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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Srinivas_Itech So that I understand. For each Order ID, for the last row (last Log Date), you want the difference between the earliest Close date for that Order ID and the last log date for that Order ID, correct?

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