Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
@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
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.
@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
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.
Speedy response. I'll check it out
Thanks Greg
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
20 | |
20 | |
13 | |
12 |
User | Count |
---|---|
41 | |
28 | |
25 | |
23 | |
21 |