Forum Discussion

Neiltc's avatar
Neiltc
Frequent Visitor
8 years ago
Solved

Querying between two dates

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?
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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.