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
fr_jc
Advocate I
Advocate I

New column to retrive an earlier row date

Hi, I am hoping someone may point me in the right direction with creating a column either by DAX or power query.  I've spent a bit of time looking for an example but can't seem to find one that I can use with the conditions i have.

 

I am wanting to add a calculated column of the days between dates of an account which exist on different rows, or if possible just return the previous date.

 

As an example I am trying to replicate the last two rows in this table.  Looking at the account of a row I am wanting to return the accounts previous original date.  My table has accounts which have multiple occurences where I am wanting just the previous date seen for each row.

 

Account

Original Date

Subsequent Follow Up Date

Days between previous original date of Account

Previous Date

2002

3/02/2021

1/03/2021

  

1001

1/05/2021

15/06/2021

  

3003

3/05/2021

31/05/2021

  

1001

15/06/2021

21/06/2021

51

1/05/2021

2002

1/07/2021

5/07/2021

20

3/02/2021

2002

5/07/2021

9/07/2021

8

1/07/2021

3003

31/07/2021

5/08/2021

94

3/05/2021

4004

1/09/2021

4/09/2021

  

 

 

Many thanks in advance

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@fr_jc 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@Greg_Deckler 

 

Many thanks Greg.  The bit I was strugling with was filtering by account and with your help in that article  there I could see what i was missing.

 

MAXX(
FILTER(
Warranty,
Warranty[Original Date] < EARLIER(Warranty[Original Date]) &&
Warranty[Account] = EARLIER(Warranty[Account])
),
Warranty[Original Date]
)

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@fr_jc 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

Many thanks Greg.  The bit I was strugling with was filtering by account and with your help in that article  there I could see what i was missing.

 

MAXX(
FILTER(
Warranty,
Warranty[Original Date] < EARLIER(Warranty[Original Date]) &&
Warranty[Account] = EARLIER(Warranty[Account])
),
Warranty[Original Date]
)

Speedy response.  I'll check it out

 

Thanks Greg

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.