Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ssharm43
Helper I
Helper I

calculate 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 CaseLifecycle StageLifecycle Stage Completion DateActual Stage 1 DateFinal DatesFinal Dates Notes
SWIMStage 19/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
SWIMStage 2 8/1/20189/1/2018Date 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

 

BUUse CaseLifecycle StageCurrent StageMax StageLifecycle Stage Completion DateActual Stage 1 DateFinal DatesFinal Dates Notes
BU1AssuranceStage 1Stage 1Stage 5 8/1/20188/1/2018Date from Actual Stage 1 Date
BU1AssuranceStage 2Stage 1Stage 5 8/1/20189/1/2018Stage not in progress. Date should match Stage 2 for rest of Use Cases
BU1AssuranceStage 3Stage 1Stage 5 8/1/20187/20/2019Stage not in progress. Date should match Stage 3 for rest of Use Cases
BU1AssuranceStage 4Stage 1Stage 5 8/1/20187/22/2019Stage not in progress. Date should match Use for rest of Use Cases
BU1AssuranceStage 5Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Engage for rest of Use Cases
BU1AssuranceStage 6Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1AssuranceStage 7Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1AssuranceStage 8Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases
BU1SWIMStage 1Stage 2Stage 59/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
BU1SWIMStage 2Stage 2Stage 5 8/1/20189/1/2018Date from SWIM Stage 1 Complete
BU1SWIMStage 3Stage 2Stage 5 8/1/20187/20/2019Stage not in progress. Date should match Stage 3 for rest of Use Cases
BU1SWIMStage 4Stage 2Stage 5 8/1/20187/22/2019Stage not in progress. Date should match Use for rest of Use Cases
BU1SWIMStage 5Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Engage for rest of Use Cases
BU1SWIMStage 6Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1SWIMStage 7Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1SWIMStage 8Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases
BU1NDOStage 1Stage 5Stage 510/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
BU1NDOStage 2Stage 5Stage 58/30/20198/1/20189/1/2018Date from SWIM Stage 1 Complete
BU1NDOStage 3Stage 5Stage 59/30/20198/1/20187/20/2019Date from SPA Stage 2 Complete
BU1NDOStage 4Stage 5Stage 53/5/20208/1/20187/22/2019Date from SPA Stage 3 Complete
BU1NDOStage 5Stage 5Stage 5 8/1/20183/5/2020Date from NDO Use Complete
BU1NDOStage 6Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1NDOStage 7Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1NDOStage 8Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases

 

1 ACCEPTED SOLUTION

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

New column.

View solution in original post

8 REPLIES 8
amitchandak
Super User
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

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 :

 

BUUse CaseLifecycle StageCurrent StageMax StageManual Completion DateActual Stage 1 DateFinal Dates 1Final Dates Notes
BU1AssuranceStage 1Stage 1Stage 5 8/1/20188/1/2018Date from Actual Stage 1 Date
BU1AssuranceStage 2Stage 1Stage 5 8/1/20189/1/2018Stage not in progress. Date should match Stage 2 for rest of Use Cases
BU1AssuranceStage 3Stage 1Stage 5 8/1/20187/20/2019Stage not in progress. Date should match Stage 3 for rest of Use Cases
BU1AssuranceStage 4Stage 1Stage 5 8/1/20187/22/2019Stage not in progress. Date should match Use for rest of Use Cases
BU1AssuranceStage 5Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Engage for rest of Use Cases
BU1AssuranceStage 6Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1AssuranceStage 7Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1AssuranceStage 8Stage 1Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases
BU1SWIMStage 1Stage 2Stage 59/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
BU1SWIMStage 2Stage 2Stage 5 8/1/20189/1/2018Date from SWIM Stage 1 Complete
BU1SWIMStage 3Stage 2Stage 5 8/1/20187/20/2019Stage not in progress. Date should match Stage 3 for rest of Use Cases
BU1SWIMStage 4Stage 2Stage 5 8/1/20187/22/2019Stage not in progress. Date should match Use for rest of Use Cases
BU1SWIMStage 5Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Engage for rest of Use Cases
BU1SWIMStage 6Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1SWIMStage 7Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1SWIMStage 8Stage 2Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases
BU1NDOStage 1Stage 5Stage 510/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
BU1NDOStage 2Stage 5Stage 58/30/20198/1/20189/1/2018Date from SWIM Stage 1 Complete
BU1NDOStage 3Stage 5Stage 59/30/20198/1/20187/20/2019Date from SPA Stage 2 Complete
BU1NDOStage 4Stage 5Stage 53/5/20208/1/20187/22/2019Date from SPA Stage 3 Complete
BU1NDOStage 5Stage 5Stage 5 8/1/20183/5/2020Date from NDO Use Complete
BU1NDOStage 6Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1NDOStage 7Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1NDOStage 8Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Advocate for rest of Use Cases
BU1SPAStage 1Stage 5Stage 510/1/20188/1/20188/1/2018Date from Actual Stage 1 Date
BU1SPAStage 2Stage 5Stage 57/20/20198/1/20189/1/2018Date from SWIM Stage 1 Complete
BU1SPAStage 3Stage 5Stage 57/22/20198/1/20187/20/2019Date from SPA Stage 2 Complete
BU1SPAStage 4Stage 5Stage 55/7/20208/1/20187/22/2019Date from SPA Stage 3 Complete
BU1SPAStage 5Stage 5Stage 5 8/1/20183/5/2020Date from NDO Use Complete
BU1SPAStage 6Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Adopt for rest of Use Cases
BU1SPAStage 7Stage 5Stage 5 8/1/20183/5/2020Stage not in progress. Date should match Optimize for rest of Use Cases
BU1SPAStage 8Stage 5Stage 5 8/1/20183/5/2020Stage 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors