Capacity Hours by employee based on work week (work days with holidays)

[electrobrit]

Trying to get the capacity or available Hours (8 hours per day) by employee based on work week (work days with holidays)
I have the work day and work hours in my calendar table
my calendar table is related to my data (timedetail) table by period end date


I have tried different dax to get capacity and nothing is working. (you can see field "capacity" in both calendar and timedetail)
The result I want is this.
Date=period end (week ends every saturday)-this below would be assuming the weekending 9/17 has a holiday

Name                Date              Capacity
EmployeeA       9/10/2022         40
EmployeeA       9/17/2022         32
EmployeeB       9/10/2022         40
EmployeeB       9/17/2022         32

I assume if I could get the above to work, I could also filter by department and it would show correctly too.

Here is a sample pbix 

thank you in advance, I expected this to be relatively simple but I'm stuck. 

[electrobrit]

@sevenhills or anyone
I changed the relationship but it didn't do anything. Data types are "dates", you said to check them, I didn't change anything.
The working hours are correct by date, by Calendar period end and Time Detail period end date until you add the employee name and all the working hours change to 0.
Thank you for your input but it still doesn't work.
Surely there is a solution.

[sevenhills · Accepted solution]

Hi

a) I see you as post patron member. Hence, I was pointing things where it might be wrong and posted sample DAX table and values, so that it helps you to verify.
It is not that I am saying it is wrong with your implementation.

Data/Model 
We need to fix things ... I tried like below, with my code from scratch:

Calendar = 
 ADDCOLUMNS( 
     CALENDAR(DATE(2021,12,1),(DATE(2024,6,30)))
     , "Month Name", Format([Date], "mmmm")
     , "Month#", Month([Date])
     , "period start", [Date] - 6
     , "period end", [Date] - WEEKDAY([Date],2) + 6
     , "Quarter #", Format([Date], "q")
     , "Today", TODAY()
     , "WeekDay", FORMAT([Date], "dddd")
     , "Weekday#", WEEKDAY([Date])
     , "WeekNum", WEEKNUM([Date])
     , "Working Houors -Capacity By Day", IF (WEEKDAY([Date], 2 ) IN { 1, 2, 3, 4, 5 }, 8, 0 )
     , "Year", YEAR([Date])
 )

Later added columns

WorkingDay = If ( ISBLANK([Holiday]), If ([Weekday#] in {1 , 7}, 0, 1), 0)

Working hours = CALCULATE(SUM([WorkingDay]) * 8)

Period Working Hours = 
 var _PeriodEndDate = [period end] 
return CALCULATE(count([Date]) * 8, filter(all('Calendar'), [period end] = _PeriodEndDate && ISBLANK([Holiday]) && [Weekday#] in {2, 3, 4, 5, 6}))

The calendar table looks like this...

now the below table looks good in my view:

Table = SUMMARIZE( TimeDetail, TimeDetail[full_name], 'Calendar'[period end]
, "Sum Test", Sum(TimeDetail[hours_worked])
, "Sum Test Capacity", sum('Calendar'[Working Houors -Capacity By Day]) 
, "Sum Period Hours", Sum('Calendar'[Period Working Hours])
)

Coming to visualization: it is looking good

calendar period end, Period Working Hours, time details period_end_date

Coming to your final output expected: 

Note: I cannot match your output in the post above. This is due to the data you have holiday as on Sept 5 and expecting 40 hours. It should be 32 hours for that period due to holiday. 

(You can rename the columns in the output visualization to your needs).

b) Could you (re-)upload the pbix file with your changes? I will take a look

You said "I changed the relationship but it didn't do anything. Data types are "dates"
The working hours are correct by date, by Calendar period end and Time Detail period end date until you add the employee name and all the working hours change to 0."

In my view, rollup is not happening with period end date but instead happening from date column in the calendar. This is due to period end date is mapped to date. 

Thanks

[electrobrit]

It worked! I don't see anything as an issue.

New Sample pbix 

I do have to go clean this up a bit, but that was completely stumping me. I can't thank you enough. I learned some new things from how you set this up too. THANK YOU

[sevenhills]

Thank you for your words. I am happy that it got resolved.

I will accept kudos 🙂 

[electrobrit]

This looks like you got it! Oh I'm so happy to see it work.  Ok, will get it in the pbix and upload when I get a moment this afternoon. 

[sevenhills]

I spent some time and want you to look first into this and focus the model relationships or measures before going into actual error!

create new page for testing

a) Date slicer using Calendar[Date] and set the range as 9/1/2022 and 9/30/2022

b) Create table visualization

    period end, and working hours from Calendar. Looks Good!

c) Let us add timedetails period end column to this visualization and 

 see with calendar period end, calendar working hours, time details period_end_date

Interesting?

a) check the data types of period end (minor but not the error reason)

b) check the relationship you have as Many to one, which is wrong in my view.

my point is that you are using Calendar Date to Time Detail Period End in the relationship, which is typically on weekend and the hours is zero.

Hope this helps!

[sevenhills]

Alternatively you can test by creating temp tables using these DAX and see the results for capacity columns, which is zero.

Table = SUMMARIZE( TimeDetail, TimeDetail[full_name], 'Calendar'[period end], "Sum Test", Sum(TimeDetail[hours_worked]), "Sum Test Capacity", sum('Calendar'[Working Houors -Capacity By Day]) )

[electrobrit]

@sevenhills 

This makes sense: "my point is that you are using Calendar Date to Time Detail Period End in the relationship, which is typically on weekend and the hours is zero"
So is there a solution if our period end date is a Saturday and we do our work week in that period? you said check the relationship.  I am wondering what it should be.

your table still gives 0 for capacity

[electrobrit]

It seems like if you could summarize name by working hours, I might could get available hours for a week. I have tried a number of dax and can't get it so I'm not doing something correct.

our reporting period is by period end. However, if a period ends on Saturday and you want working hours by week, what would be the correct way to do this so it's not zero because period end is a saturday?