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

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.

Regular Visitor

Avg of CumNo of days taken with no of projects - Reducing the no of days hold

Result i want.pngReult i am getting.pngTable Data.pngI want to calculate average Days taken for Projects (All Projects), But We have minus the Days the Project was on hold due to waiting for Customer Feedback. This data has to be plotted as month on month and if it is on hole at end of the month it shall not be counted for that month and when it is reassigned the days to be added.



Community Support
Community Support


I have some doubts ,can you explain it?

1.Which picture is the original data, "Result I am getting“  or "No of commulative Days"?

2.When the project is closed, the month is not calculated This month is empty When the project is opened again, is it the effect you want to achieve, if so, you need to provide some relevant example data for when these two projects start and when they end, or do you just want to calculate the relevant average, based on the picture you give, my understanding is that you want to calculate the average.

Based on the expected result you have offered.

You can create a new calculated column.



Average = SWITCH(TRUE(),[Project 1]=BLANK()&&[Project 2]=BLANK(),0,[Project 1]<>BLANK()&&[Project 2]<>BLANK(),DIVIDE([Project 1]+[Project 2],2),OR([Project 1]=BLANK(),[Project 2]=BLANK()),DIVIDE([Project 1]+[Project 2],1))






Best Regards!

Yolo Zhu

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



i need to calculate average no of days,  of all projects, but each an every project opened at different dates and closed at different dates, and few projects would have been reopened /reactivated and closed several times,  We need to calculate average open /Active days of all projects,   

Thank you for your reply , here i have given project 1 and project 2 as a sample data, But we have "N" no of projects.  Can you help me for the formula in this case 

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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