Forum Discussion
Column Header Name in Calculation
MFelix,
I need to calculate remaining hours each month by contractor over a 1 year period. Subtract hours worked from PO Hours during active contract dates. If a contractor works no hours subtract hours available to work that month. This gives us a burndown of hours in the contract. If the contract hasn't started yet or if the contract has ended, subtract 0.
The problem we are having is that the hours available to work are subtracted before the contract begins and after the contract ends. I originally wanted to use the column headers to look up what month they were so I could assign a numerical value to each Month.Year and compare that to the start and end of each contractors Month.Year.
To elaborate I would look up September 2018 and assign it a 9. If a specific contractor’s contract started in October (9<10) it would not bill any hours for September. This is important because I want to see the total number of hours each contractor will use by month over the year using both a visual and a table.
Using the same contractor as an example if they can work 160 hours in November but they have not submitted their hours yet I would want to use the 160 for the burndown amount unless the hours are submitted.
Data and PBI file examples are below:
Excel Data: https://app.box.com/s/yzg2tjiea7x34gepvjk4dtj1j30kliaj
PBI File: https://app.box.com/s/3yzr3vog8kmljhyftgd3qpgthpzqho3y
Hi Anonymous,
Not really sure if this is what you need. But please check the PBIX file attached, Iremove all your calculated columns and created 2 measures highlited in green on the table.
However I still think that the best solution would be to unpivot the columns since that would allow you to use time inteligence formulas.
As I said check the two measures I created and if it's expected result if yes I will transform the rest of the data, since it's not yet looking at year and so on.
Regards,
MFelix
The file you attached is working very well and is almost exactly what I need; thank you so much for the help so far. I very much appreciate it. Besides having the remaining months I need to subtract the amount of workable hours that a contractor could work if their PO was active during that month. When I was originally trying to solve this I was trying to do date comparisons but they would not evaluate properly. Do you have any way to also incorporate this? Just for your information, we will be looking at the data at the contractor level if you have to adjust for that in the calculation.
For example contractor "Last8207, First622"'s PO was active in September but they did not submit any hours. I need to subtract the workable hours for that month and any future months that the PO is active. This amount will vary each month so whatever hours you use in your calculation I could easily adjust.
Please transform the data to look at the year as you offered. If you make any changes to the data let us know what you changed so we can replicate it. I also do not fully understand the calculations that you are using, if you do not mind could you explain it so I can learn for the future?
Thank you again for you help.