topic Re: calculate Dates based on conditions in DAX Commands and Tips

calculate Dates based on conditions

ssharm43 — Sun, 06 Sep 2020 23:23:27 GMT

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

Re: calculate Dates based on conditions

amitchandak — Mon, 07 Sep 2020 00:33:31 GMT

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

Re: calculate Dates based on conditions

ssharm43 — Mon, 07 Sep 2020 02:54:35 GMT

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.

Re: calculate Dates based on conditions

amitchandak — Mon, 07 Sep 2020 05:00:38 GMT

ssharm43 , I do not see any date like 7/20 or 7/22 in the sample data. Please find the pbix with modifed logic

Re: calculate Dates based on conditions

ssharm43 — Mon, 07 Sep 2020 05:35:15 GMT

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

Re: calculate Dates based on conditions

amitchandak — Mon, 07 Sep 2020 06:06:16 GMT

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.

Re: calculate Dates based on conditions

ssharm43 — Mon, 07 Sep 2020 06:35:25 GMT

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.

Re: calculate Dates based on conditions

amitchandak — Mon, 07 Sep 2020 06:58:30 GMT

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

New column.

Re: calculate Dates based on conditions

ssharm43 — Mon, 07 Sep 2020 07:23:07 GMT

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. 🙂