Latest Enterprise DNA Initiatives

Access the Available Hours and bring accross to Tktable to calculate a measure!

HI All,

Here is my data model
Scenario.xlsx (1.1 MB)

What is the business case problem?

In Tktable I have the Reg Hrs for each employee. However, in EmployeeeAvailableHours there is information about the employees daily available hours.

What am I trying to achieve?

I want a way to get each employee hours in the EmployeeAvailableHours table for each date that is in FYCalendar date table and for each employee in the Employee table in the Tktable so I can compare the reghrs to the available hours and calculate utilisation %

Twist?

Instead of each employee available hours per day I want to aggregate all of the hours per month and transfer only the monthly figures to Tktable. So for each day in the Tktable were we have reg hours alongside it I want the total employeeavailablehours for the month.

I hope this makes sense. If it is not clear please let me know.

I have attached the case study file below and I appreciate all your efforts for helping me in advance.

@BrianJ @AntrikshSharma this might be around your alley to knock this apart. Thanks in advance

P

@ambepat ,

Eager to take a swing at this one. Can you please provide your work-in-progress PBIX file?

If it contains any confidential/sensitive data, here are some strategies for masking it or providing representative simulated data.

Thanks.

  • Brian

Hi @ambepat,

Your file didn’t include a mock up with desired results, so I’m not sure I interpreted it correctly…
Added a boolean filter column to the EmployeeAvailableHours table

.
Added a MonthlyAvailHrs column to your Tktable

.
Here’s your sample:
eDNA - employee available hrs.pbix (94.1 KB)

I hope this is helpful.

@BrianJ

Here you are.

Thanks in advance
Scenario.pbix (141.8 KB)

@Melissa how do you modify just to include working days?

@ambepat ,

Looks like @Melissa has already nailed this one in typically elegant fashion. Makes total sense to me to do this in PQ, rather than DAX.

Nice solution, @Melissa!

  • Brian

Hi @ambepat,

Wouldn’t it be weird to have a weekenddate in the EmployeeAvailableHours? If not, no worries you can exclude them by adding this List.Select function.

Looks like you already had a weekend date included…

@brianj thanks. I thought a solution in DAX will be better but @Melissa is proving you can do it in PQ although I am so confused right now.

Thanks @Melissa. Still looking at it to apply it as not sure if I fully understand the solution but will let you know if there are any other things.

Hello Patrick,
here are my two cents provided that I understood correctly your requirements:
“I want a way to get each employee hours in the EmployeeAvailableHours table for each date that is in FYCalendar date table and for each employee in the Employee table in the Tktable so I can compare the reghrs to the available hours and calculate utilisation %”
I have to admit that a bit more data in the “Scenario.xlsx” would help in order to better check up the flow. However, this is what I did.
First I removed all the tables in the provided pbix except the FYCalendar and the supporting query, then extracted the tables in your spreadsheet. (renamed the tables adding “-Scenario” in the end)

  1. I merged Tktable with EmployeeAvailableHours so that I could get the Hours per day in the table mainly for being able to create relationships between tables. This is how it looks now:
  2. Tweaked a bit here and there and the tables are in sync and could quickly identify the Utilisation.

    I attached the pbix to see the relationships
    Scenario-sorinlinx.pbix (143.4 KB)

Hope it helps.
Cheers.

HI @Melissa thanks for this solution but when I tried to apply it to my model it just was too big. I failed to mention that the employeeavailablehours table has about 200k rows.

So just for completeness I have managed to anonymise the data and I have attached it here to give you the full picture of what my data looks like and what I am trying to achieve.

My end result

I want to be able to add on the TKtable a column with available hours (This comes from the employeeavailablehours table) and another column annual leave hours (this comes from the Annualeavehrs table).

Here is an screenshot of an example of the end result for employee 375

So for Employee 375 his total available hours for the month of july (only working days) is 176 and the amount of annual hours (in the current month July) he has is zero (because although he submitted them we only accept hours approved from description column of the annualleavehrs table).

I hope this is much clearer and I have include a sample PBIX file to work on.

ScenarioDNA.pbix (372.3 KB)

Thanks very much in advance.

Hi @ambepat,

Could you please share the ScenarioDNA.xlsx source file as well?
Thanks!

Hi @Melissa here you are.

Thanks you very much.
ScenarioDNA.pbix (372.3 KB)

@ambepat LOL that’s the PBIX again, need the xlsx

@Melissa sorry I am getting confused. Here you are.
ScenarioDNA.xlsx (5.8 MB)

Hi @ambepat,

Go to the FileLocation parameter and select your source file location from the list.
Then select close and apply…
ScenarioDNA.pbix (346.1 KB)

I hope this is helpful

Hi @Melissa

Thanks for this. Will look into it and get back to you if that is ok. Need a bit of time to digest the solution.

Appreciate it.

P

@ambepat sure.
If you have any specific questions, just let me know - here in the thread

All the best.