Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago

Calculating Time Entered by Month

I have a column called "SOW Hours" and a column called "DurationActual" which is time entered against the job number. 

 

I need to calculate the %Completion based on hours (DurationActual/SOW Hours) But on a monthly basis.

 

Example: Job was executed in January with SOW Hours= 100.  In January DurationActual= 25, in February DurationActual=25.  So far the job is 50% complete, but I need to calculate completion each month. 25% complete in Jan, 25% complete in February, etc. 

 

Would I use the TotalMTD function? Or would that not serve my purpose? 

Need Help

3 Replies

  • paulag's avatar
    paulag
    Advocate I
    9 years ago

    Can you share a screen shot of your data.  Do you have a seperate column for each DurationActual Month or are the months down the rows?

    • bhmiller89's avatar
      bhmiller89
      Helper V
      9 years ago

      The months are down the rows. Each DurationActual is based on a Job Report/Time Entry. Jobs will have multiple, sometimes thousands, of DuratonActuals associated with them

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi bhmiller89,

        Please try create two calculated columns to get SOW Hours and DurationActual.

        SOW Hours=CALCULATE(SUM(Table[Hours]),ALLEXCEPT(Table,Table[Hours]))

DurationActual=CALCULATE(SUM(Table[Actual]),ALLEXCEPT(Table,Table[Actual]))

        Then create finally calculated to get expected result.

        Result=Table[DurationActual]/Table[SOW Hours]

         

        If this doesn'y solve your issue, please post sample data and list desired result for further analysis.

        Best Regards,
        Angelia