topic Date status column in DAX Commands and Tips

Date status column

JoeHazelton — Thu, 18 Aug 2022 11:52:45 GMT

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

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

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

Re: Date status column

DimaMD — Thu, 18 Aug 2022 12:19:15 GMT

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

Re: Date status column

DimaMD — Thu, 18 Aug 2022 12:39:39 GMT

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" )

Re: Date status column

JoeHazelton — Thu, 18 Aug 2022 12:57:42 GMT

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