Calculating Time Entered by Month

[bhmiller89]

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? 

[paulag]

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]

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]

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