Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Srinivas_Itech
New Member

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 IDLog DateClose DateDate_Diff
Temp_00101/01/202210/01/20220
Temp_00105/02/202215/02/2022 
Temp_00201/06/202225/06/2022 
Temp_00202/07/202210/07/2022 
Temp_00220/07/202227/07/2022 
Temp_00208/08/202230/08/2022 
Temp_00120/01/202225/01/2022Required Diff From Close Date (10-01-2022) To Next Log Date (20-01-2022)
1 ACCEPTED SOLUTION

@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/3395....
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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @Srinivas_Itech 

please try

 

Number of Days =
VAR CurrentLogDate = 'Table'[Log Date]
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) )
VAR TableBefore =
    FILTER ( CurrentIDTable, 'Table'[Log Date] > CurrentLogDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Log Date] )
VAR PreviousCloseDate =
    MAXX ( PreviousRecord, 'Table'[Close Date] )
RETURN
    IF ( NOT ISBLANK ( PreviousCloseDate ), CurrentLogDate - PreviousCloseDate )

*Update

The solution is updated with a small correction to blank out first date

 

Hi Sir ,

 

I have tried but not getting exact value getting very high value please suggest 

@Srinivas_Itech 
Oh! a small mistake in the code (should be "<" not ">"). Apologies for that. Please try

Number of Days =
VAR CurrentLogDate = 'Table'[Log Date]
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Order ID] ) )
VAR TableBefore =
    FILTER ( CurrentIDTable, 'Table'[Log Date] < CurrentLogDate )
VAR PreviousRecord =
    TOPN ( 1, TableBefore, 'Table'[Log Date] )
VAR PreviousCloseDate =
    MAXX ( PreviousRecord, 'Table'[Close Date] )
RETURN
    IF ( NOT ISBLANK ( PreviousCloseDate ), CurrentLogDate - PreviousCloseDate )
Srinivas_Itech
New Member

Hi Sir ,

 

I Tried but i need result as below kindly help sir 

 

Srinivas_Itech_0-1664805673009.png

 

@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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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 

@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/3395....
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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Awesome Sir thanks  a lot sir

Greg_Deckler
Super User
Super User

@Srinivas_Itech Try:

Date_Diff Column =
  ( [Close Date] - [Log Date] ) * 1.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.