Hours available per week based on a standard hours (then adjusting based on conditions

[electrobrit]

Hoping someone can assist with this issue: (seems simple but it's not for me)
I am trying to get a table with these fields from the data in the pbix file attached.

Note: I have a condition when it's listed as "Employee" that "final adjusted hours" Is available hours minus total PTO
If "Contractor"-final adjusted hours is equal to "Hours Worked" which is in the data
If the person is "Andy"-final adjsuted hours is equal to "hours worked"

I am doing everything in power query because I was having issues with the conditional columns in Dax. I've spent a lot of time trying both ways and that seemed to make more sense.
When i get the columns added and do the table visual it doesn't work. There are 2 lines for each date if the person has PTO. (the sample pbix attached shows what it's doing)
I would like it to be by person, by date (one row per date per person with data)-this is how it should be below.

I have added the pbix with 2 tabs where I tried it different ways and have issues on both. I feel like it's the standard hours table (I've merged and tried to not merge)
Can someone help me get to final table I am trying to get to? I am stumped and I've searched and searched but at the point I don't even know what it's doing and what to search anymore.
If you have a better idea using dax or anything, I'm ok with whatever works! THANK YOU in advance

Sample PBIX 

[v-yangliu-msft · Accepted solution]

Hi  @electrobrit ,

You can try the following dax

Column =
VAR _PTOSUM=SUMX(FILTER(ALL(TimeDetail),'TimeDetail'[period_end_date]=EARLIER('TimeDetail'[period_end_date])&&'TimeDetail'[resource_type_code]=EARLIER('TimeDetail'[resource_type_code])&&
CONTAINSSTRING('TimeDetail'[task_type_name],"PTO")=TRUE()),[PQ_TOTAL PTO])
RETURN
SWITCH(
    TRUE(),
    'TimeDetail'[full_name]="Andy",[hours_worked],
    'TimeDetail'[resource_type_code]="Employee",[PQ.Available Hours] - _PTOSUM,
    'TimeDetail'[resource_type_code]="Contractor",[hours_worked])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[electrobrit]

I was able to get this to add the column with no errors, but it is not working like it should. It still does 2 rows per person if they have PTO and the final number is sometimes negative. 

[electrobrit]

It seems like the one column you suggested isn't achieving the goal of the table I said I was trying to get (screenshot above). Anyone else have any ideas? Am I missing something in explaining or is this just not possible or a stumper for everyone?