Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@ssharm43 , Check the file now. Remove the not required var.
New column.
@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
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.
@ssharm43 , I do not see any date like 7/20 or 7/22 in the sample data. Please find the pbix with modifed logic
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 |
@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.
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. 🙂
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.
@ssharm43 , Check the file now. Remove the not required var.
New column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |