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

4 Replies

  • 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.

     

     

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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's avatar
      Neiltc
      Frequent Visitor
      8 years ago

      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's avatar
        v-juanli-msft
        Community Support
        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