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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Chintan
Frequent Visitor

Matrix to display total days spent on the task - Conditional formatting based on late or on-time.

 
6 REPLIES 6
parry2k
Super User
Super User

@Chintan based on your reply, it means there must be data to show which stage each task is. Can you share some sample data in the table format?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Please see below sample data. I have sequence the Stages from New to Manage in a raw. I mean first stage is New folllowed by Preparation and so on. Please note after manage stage, the task is completed. Here in below screenshot, Task 1 is delayed because it spend 6 days in Stage New and Stage New which first stage should be completed within 4 days from created date. By the end of 4th day, the team should have completed Stage New and update it's status to Stage Preparation. While Task 5 is on time beacuse within 19 days, it's already in Manage Stage. 

 

New - 1 to 4 Days
Preparation - 5 to 8 Days

Calculation - 9 to 18 Days
Approved - 19 to 22 Days

Manage - 23 to 28 Days



Hope it make sense. 

Chintan_0-1695089430087.png

 

Many thanks

parry2k
Super User
Super User

@Chintan I think I understood what you are looking for and I have some questions:

 

- how do you find out if the task is on time or delayed?

- what happens if it is more than 28 days?

- what if tasks are already completed? Does that get added to it? If yes, if a task is already completed, do you still want to calculate the day based on today's date which in my opinion doesn't make sense?

 

I think you need to look at the bigger picture and ask your questions accordingly.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Thank you for your response.

 

-- Regarding on time or delayed, each task has five stages and it should be completed in 28 days. Stage 1 should be completed within 4 days of startdate, Stage 2 should be completed within 8 days of startdate, Stage 3 should be completed within 18 days of startdate, Stage 4 should be completed within 22 days of startdate and last Stage 5 should be completed within 28 days of startdate. For example; if Task 1 is in Stage 2 (which means it should be completed within 8 days of startdate) doesn't change it's status to Stage 3 in 8 days then it should be grey colour else blue colour.

 

--  if it more than 28 days and still task is not completed we will just colour it grey from Day 1 and Day 28.

 

-- No, completed task will be filter out and will not be included. So all good with Completed.

 

Let me know if you have further question. I am excited for the solution. 

 

Thank you 🙂

Ritaf1983
Super User
Super User

Hi @Chintan 

It is a calassic cantt chart.

Please refer to the linked tutorials

https://www.youtube.com/watch?v=UVDR3HFncKU

https://www.youtube.com/watch?v=8pbFvBMT9L8

https://www.youtube.com/watch?v=cdUg8LfarCg

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thank Ritaf for your time and response.

 

The videos are very helpful however I am not looking for Gantt Chart, it's kinda similar but what I want to achieve is still different to Gantt Chart and videos are all about Gantt Chart. 

I want to write a DAX that count total days from the startdate and diaplay total days from Day 1 in the Matrix as shown in the screenshot. For example; first task in the screenshot took 21 days since startdate.
Many thanks. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors