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.
I have an excel sheet with two columns, A is date project is due, B is date the project is fullfilled. I am trying to calculate the amount of projects delivered on time each month, can anyone help?
Solved! Go to Solution.
Hi @Neiltc
There is a solution to do it in Power BI, if you wnat to do it in excel, you need to post on excel forum.
I assume that projects that were not completed on time are which fulfilled date is larger than due date.
Also “each month” is determined by due date.
So I can create calculated columns
month = MONTH([due date])
complete = IF([fullfilled date]<=[due date],1,0)
percentage of not completed per month =
CALCULATE (
COUNT ( Sheet1[complete] ),
FILTER ( ALLEXCEPT ( Sheet1, Sheet1[month] ), [complete] = 0 )
)
/ CALCULATE ( COUNT ( Sheet1[complete] ), ALLEXCEPT ( Sheet1, Sheet1[month] ) )
Best Regards
Maggie
Hi @Neiltc
Could you consider do this with Power BI Desktop?
It will be easy to do in this way.
"I am trying to calculate the amount of projects delivered on time each month"
This means
calculate the amount of projects which date A=dateB, right?
Best Regards
Maggie
Maggie, I am actually trying to do this in BI but thought it would be easier through Excel. The goal is to see in each month the percentage of projects that were not completed on time. is there a PowerBI solution for this?
Thank you
Hi @Neiltc
There is a solution to do it in Power BI, if you wnat to do it in excel, you need to post on excel forum.
I assume that projects that were not completed on time are which fulfilled date is larger than due date.
Also “each month” is determined by due date.
So I can create calculated columns
month = MONTH([due date])
complete = IF([fullfilled date]<=[due date],1,0)
percentage of not completed per month =
CALCULATE (
COUNT ( Sheet1[complete] ),
FILTER ( ALLEXCEPT ( Sheet1, Sheet1[month] ), [complete] = 0 )
)
/ CALCULATE ( COUNT ( Sheet1[complete] ), ALLEXCEPT ( Sheet1, Sheet1[month] ) )
Best Regards
Maggie
create a helper column in the excel sheet and write an IF statement to determine if it meets the criteria. than have a formula count the instances where you get a positive result. This data can then be added to your table if you need it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.