Forum Discussion
Helper Icalculate Dates based on conditions
Hello everyone !!
Please help me on this calculation, I am stuck on this from last 2 weeks.
I am trying to populate Final Dates column based on Final Dates Notes. Below is the data and description of the columns.
Final Dates column is based on Minimum dates taken from Actual Stage 1 completion date and Lifecycle Stage Completion Date for BU.
logic is :
1. for Stage 1, populate Min(Actual Stage 1 Date) and for remaining Stage, populate Min(Lifecycle Stage Completion Date).
For Example :
Final Dates for Lifecycle Stage 1 date is Min(Actual Stage 1 Date) and for final dates Stage 2 is Min(Lifecycle Stage Completion Date) of Stage 1.
| Use Case | Lifecycle Stage | Lifecycle Stage Completion Date | Actual Stage 1 Date | Final Dates | Final Dates Notes |
| SWIM | Stage 1 | 9/1/2018 | 8/1/2018 | 8/1/2018 | Date from Actual Stage 1 Date |
| SWIM | Stage 2 | 8/1/2018 | 9/1/2018 | Date from SWIM Stage 1 Complete |
BU = Business Unit
Use Case = group of conditions
Lifecyscle Stage = Stage for BU for each Use Case
Current Stage = Stage where currently BU is sitting in current date
Max Stage = Stage where BU reached across all the use case
Lifecycle Stage Completion Date = the date when a Lifecycle stage completed
Actual Stage 1 Date = Dates when first Stage got completed
Final Dates = Minimum Dates from Lifecycle Stage Completion Date and Actual Stage 1 Date (this is what I have to calculate)
Final Dates Notes = Conditions for each row in Final Dates on how it should populate
| BU | Use Case | Lifecycle Stage | Current Stage | Max Stage | Lifecycle Stage Completion Date | Actual Stage 1 Date | Final Dates | Final Dates Notes |
| BU1 | Assurance | Stage 1 | Stage 1 | Stage 5 | 8/1/2018 | 8/1/2018 | Date from Actual Stage 1 Date | |
| BU1 | Assurance | Stage 2 | Stage 1 | Stage 5 | 8/1/2018 | 9/1/2018 | Stage not in progress. Date should match Stage 2 for rest of Use Cases | |
| BU1 | Assurance | Stage 3 | Stage 1 | Stage 5 | 8/1/2018 | 7/20/2019 | Stage not in progress. Date should match Stage 3 for rest of Use Cases | |
| BU1 | Assurance | Stage 4 | Stage 1 | Stage 5 | 8/1/2018 | 7/22/2019 | Stage not in progress. Date should match Use for rest of Use Cases | |
| BU1 | Assurance | Stage 5 | Stage 1 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Engage for rest of Use Cases | |
| BU1 | Assurance | Stage 6 | Stage 1 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Adopt for rest of Use Cases | |
| BU1 | Assurance | Stage 7 | Stage 1 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Optimize for rest of Use Cases | |
| BU1 | Assurance | Stage 8 | Stage 1 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Advocate for rest of Use Cases | |
| BU1 | SWIM | Stage 1 | Stage 2 | Stage 5 | 9/1/2018 | 8/1/2018 | 8/1/2018 | Date from Actual Stage 1 Date |
| BU1 | SWIM | Stage 2 | Stage 2 | Stage 5 | 8/1/2018 | 9/1/2018 | Date from SWIM Stage 1 Complete | |
| BU1 | SWIM | Stage 3 | Stage 2 | Stage 5 | 8/1/2018 | 7/20/2019 | Stage not in progress. Date should match Stage 3 for rest of Use Cases | |
| BU1 | SWIM | Stage 4 | Stage 2 | Stage 5 | 8/1/2018 | 7/22/2019 | Stage not in progress. Date should match Use for rest of Use Cases | |
| BU1 | SWIM | Stage 5 | Stage 2 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Engage for rest of Use Cases | |
| BU1 | SWIM | Stage 6 | Stage 2 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Adopt for rest of Use Cases | |
| BU1 | SWIM | Stage 7 | Stage 2 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Optimize for rest of Use Cases | |
| BU1 | SWIM | Stage 8 | Stage 2 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Advocate for rest of Use Cases | |
| BU1 | NDO | Stage 1 | Stage 5 | Stage 5 | 10/1/2018 | 8/1/2018 | 8/1/2018 | Date from Actual Stage 1 Date |
| BU1 | NDO | Stage 2 | Stage 5 | Stage 5 | 8/30/2019 | 8/1/2018 | 9/1/2018 | Date from SWIM Stage 1 Complete |
| BU1 | NDO | Stage 3 | Stage 5 | Stage 5 | 9/30/2019 | 8/1/2018 | 7/20/2019 | Date from SPA Stage 2 Complete |
| BU1 | NDO | Stage 4 | Stage 5 | Stage 5 | 3/5/2020 | 8/1/2018 | 7/22/2019 | Date from SPA Stage 3 Complete |
| BU1 | NDO | Stage 5 | Stage 5 | Stage 5 | 8/1/2018 | 3/5/2020 | Date from NDO Use Complete | |
| BU1 | NDO | Stage 6 | Stage 5 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Adopt for rest of Use Cases | |
| BU1 | NDO | Stage 7 | Stage 5 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Optimize for rest of Use Cases | |
| BU1 | NDO | Stage 8 | Stage 5 | Stage 5 | 8/1/2018 | 3/5/2020 | Stage not in progress. Date should match Advocate for rest of Use Cases |
ssharm43 , Check the file now. Remove the not required var.
New column.
8 Replies
- amitchandak
Super User
ssharm43 , Try a formula like this as a new columns
new column =
var stg1= Minx(filter(Table[BU] = earlier([BU]) && [Current Stage]= earlier([Current Stage])),[Actual Stage 1 Date])
var comp1= Minx(filter(Table[BU] = earlier([BU]) && [Current Stage]= earlier([Current Stage]) && not(isblank([Lifecycle Stage Completion Date])) ),[Lifecycle Stage Completion Date])
return
if([Current Stage] ="Stage1", stg1, coalesce(comp1,stg1))You may have to add or remove condition
ssharm43Thank you amitchandak for trying, but this is not giving the output I am looking for. If you see in the data Final Date is the column I am looking for as output. I have to build a line chart using this column by join with Date dim.
- amitchandak
ssharm43 , I do not see any date like 7/20 or 7/22 in the sample data. Please find the pbix with modifed logic