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
JoeHazelton
Frequent Visitor

Date status column

Dear Community,

I am looking to create a column which indicates the status of each task which covers all eventualites. I am using four date columns, Start Date, Finish Date, Actual Start & Actual Finish.

The Status' i am looking to create are; Complete, In Progress, Planned, Late Starting & Late Finishing. So far i have managed to get the below working:

 

In progress

JoeHazelton_0-1660822672746.png

I think this Status needs to only show 'In Progress' when the Actual Start date is populated but i am happy with the above formula as this gives me a good idea of what is In progress even if the programme managers havent updated the Actual Start Date field.

Planned

JoeHazelton_1-1660822703799.png

Complete

For this entry i am looking to show the status as Complete once all four date columns have been populated with dates.

Late Starting

For this entry i am looking to show the Status as Late Starting when the Start Date is passed todays date and the Actual Start field is blank/empty.

Late Finishing

For this i am looking to show the Status as Late Finishing when the Actual Start date is populated but the Finish Date is passed today date.

Any advice/guidance on this would be greatly appreciated. Happy to share more information if required.

 

Regards

 

Joe

 

1 ACCEPTED SOLUTION
DimaMD
Solution Sage
Solution Sage

@JoeHazelton I tried to simulate the example data, look maybe it will lead you to the right idea in solving your problem

measure = 
SWITCH( 
    TRUE(),
    [Start Date] > TODAY(), "Planned",
    AND([Start Date], [Finish Date]) && AND([Actual Start], [Actual Finish]) <> BLANK(), "Complete",
    AND([Start Date] <= TODAY(), [Actual Start] = BLANK()),  "Late Starting", 
    "Late Finishing"
      )


Screenshot_24.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

3 REPLIES 3
DimaMD
Solution Sage
Solution Sage

@JoeHazelton I tried to simulate the example data, look maybe it will lead you to the right idea in solving your problem

measure = 
SWITCH( 
    TRUE(),
    [Start Date] > TODAY(), "Planned",
    AND([Start Date], [Finish Date]) && AND([Actual Start], [Actual Finish]) <> BLANK(), "Complete",
    AND([Start Date] <= TODAY(), [Actual Start] = BLANK()),  "Late Starting", 
    "Late Finishing"
      )


Screenshot_24.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Wow! DimaMD..that totally works!. It covers every single possible date that my report throws out without any gaps..I cannot thank you enough.

Kind Regards

Joe

DimaMD
Solution Sage
Solution Sage

Hi, @JoeHazelton  It would be great if you could give an example of the data


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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