Querying between two dates

[Neiltc]

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?

[v-juanli-msft]

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

[Neiltc]

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

[v-juanli-msft · Accepted 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

[Anonymous]

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.