The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
This is kind of complex and I haven't been able to figure it out. I am trying to create a column that finds the number of days between a project status but the project ID must be the same. The project Establish date is held in a a project table.
Project status starts at 1.0 and goes up to 4.0. I am able to calculate the number of days it took the project to get to the status it is at from when the project was created. I want to find the amount of time it may take to get from status 3.0 to 4.0 and so on.
ID | Project ID | Modified date | Status | Days from Project Established date | Days between Status (Column I am trying to create) |
1 | 11362 | 1/20/20 | 1.0 | 0 | 0 |
2 | 11362 | 1/23/20 | 2.0 | 3 | 3 |
3 | 14598 | 1/25/20 | 3.0 | 15 | 5 |
4 | 11362 | 1/30/20 | 3.0 | 10 | 7 |
5 | 14598 | 1/30/20 | 4.0 | 25 | 10 |
Solved! Go to Solution.
Hi @ConnorH ,
Create a Calculated Column
Column =
VAR _prevstage_sameprojId_date =
CALCULATE (
MAX ( 'Table1'[Modified date] ),
FILTER (
'Table1',
'Table1'[Modified date]
< EARLIER ( 'Table1'[Modified date] )
&& Table1[Project ID]
= EARLIER ( Table1[Project ID] )
)
)
RETURN
DATEDIFF (
_prevstage_sameprojId_date,
'Table1'[Modified date],
DAY
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @ConnorH ,
Create a Calculated Column
Column =
VAR _prevstage_sameprojId_date =
CALCULATE (
MAX ( 'Table1'[Modified date] ),
FILTER (
'Table1',
'Table1'[Modified date]
< EARLIER ( 'Table1'[Modified date] )
&& Table1[Project ID]
= EARLIER ( Table1[Project ID] )
)
)
RETURN
DATEDIFF (
_prevstage_sameprojId_date,
'Table1'[Modified date],
DAY
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
I used this to calculate Days Between Loads and was wondering how I could add to it to also calculate if it was the last Load, then how many days since then to "today"?
I have a similar instance where I would really aprecaite your help.
I am trying to create a column that finds the number of workingdays (excluding weekends and bankholidays - I have a list of all bank holidays in a table) between Milestones but the Audit Review ID must be the same. I will need to calculate the difference between the End Day of Milestone 1 with the end date with Milestone 2 and so on... Not all Audit Reviews have an End Date for all 7 milestones, some only have a few as seen in example.
Audit Review ID | Milestone Type | Milestone End Date |
123456 | 1. Background Research | 01/12/2021 |
123456 | 2. Terms of Reference | 03/02/2022 |
123456 | 3. Opening Meeting | 04/03/2022 |
123456 | 4. Fieldwork and Closing Meeting | 21/03/2022 |
123456 | 5. Draft Report | 25/03/2022 |
123456 | 6. Management Responses | 29/03/2022 |
123456 | 7. Final Report | 07/04/2021 |
234567 | 1. Background Research | 01/01/2022 |
234567 | 2. Terms of Reference | 03/01/2022 |
234567 | 3. Opening Meeting | 04/02/2022 |
234567 | 4. Fieldwork and Closing Meeting | 12/02/2022 |
345678 | 1. Background Research | 01/01/2021 |
345678 | 2. Terms of Reference | 23/01/2022 |
For example, I would expect a value of 5 for the difference between (4. Fieldwork & Closing Meeting) and (5. Draft Report) for Audit 123456.
Would apprecaite your help.
@GeorgeR1 : I have similiar kind of scenario. By any chance if you find any solution . Kindly, can you please post here.
@ConnorH , Try a new column like
datediff(Table[Date], minx(filter(Table,Table[Date] <earlier(Table[Date])) ,Table[Date]),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...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
36 | |
21 | |
18 | |
15 |
User | Count |
---|---|
124 | |
39 | |
30 | |
24 | |
22 |