calculate Dates based on conditions

[ssharm43]

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

[amitchandak]

@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

[ssharm43]

Thank 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

[ssharm43]

Hi ,

It's my bad that I missed the data, I am sorry about it.

Thank you very much helping me out. and here's the more data :

BU	Use Case	Lifecycle Stage	Current Stage	Max Stage	Manual Completion Date	Actual Stage 1 Date	Final Dates 1	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
BU1	SPA	Stage 1	Stage 5	Stage 5	10/1/2018	8/1/2018	8/1/2018	Date from Actual Stage 1 Date
BU1	SPA	Stage 2	Stage 5	Stage 5	7/20/2019	8/1/2018	9/1/2018	Date from SWIM Stage 1 Complete
BU1	SPA	Stage 3	Stage 5	Stage 5	7/22/2019	8/1/2018	7/20/2019	Date from SPA Stage 2 Complete
BU1	SPA	Stage 4	Stage 5	Stage 5	5/7/2020	8/1/2018	7/22/2019	Date from SPA Stage 3 Complete
BU1	SPA	Stage 5	Stage 5	Stage 5		8/1/2018	3/5/2020	Date from NDO Use Complete
BU1	SPA	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	SPA	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	SPA	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

[amitchandak]

@ssharm43 , In stage 3, do I need to go back to one stage . I think I am missing that logic 7/20 is not related stage 3 and 7/22 with stage 4. Please let me that logic.

File with current logic.

[ssharm43]

Thank you very much. you got one more follower Added to your youtube and Linkedin. I don't use twitter so never mind.

but you made my day. 🙂

[ssharm43]

Thank you @amitchandak ,

the logic is tricky and therefore I tried to keep Final Dates Note,

here's the logic which we are missing in the calculation:

Lifecycle Stage          Final Date Logic

Stage 1                     this has Min(Actual Stage 1 Date)

Stage 2                     this has Min(Stage 1 Lifecycle Stage Date)

Stage 3                     this has Min(Stage 2 Lifecycle Stage Date)

Stage 4                     This has Min(Stage 3 Lifecycle Stage Date)

Stage 5                    This has Min(Stage 4 Lifecycle Stage Date) and so on

Thank you very much @amitchandak , your help is really appriciated.

[amitchandak · Accepted solution]

@ssharm43 , Check the file now. Remove the not required var.

New column.