Latest Enterprise DNA Initiatives

Lookupvalue and min

Hey guys,

  1. I’m trying to add 2 new columns to my business leader table: min date and max date of my timesheet table. I’ve tried with lookupvalue function and of course, it didn’t work. Don’t know why.
  2. Once this it’s done, i need a measure to show me the total hours worked for any employee, as well as the due to work, accordingly the selected dates on the report. Let me give you an example of what i need:

If i select a period in my report, like from January 1st till January 10, it’s suppose to show me only the hours of each employee that max date is after 10 of January.
Real Example: min date of Employee ID 381 is 6 of Jan. He has worked 4 days, According to timesheet table, which means 32 hours. According to our dates table, on Day Type column, he has worked 3 days on weekdays and 1 on weekend, which means the total of due work is 24 hours.
I’m attaching a file
Can anyone help me?
Thanks a lot
Pedro
TM.xlsx (24.3 KB)

Hi @pedroccamara. Check out the POTW #3 thread … very similar problem and many different approaches to solutions.
Greg

Hey Greg
Thank you for the answer @Greg.
Yes, i saw this video and although it may be the same issue, i don’t believe it’s the same thing that i need
Thanks

where is your power bi file?

didn’t add a pbi file but an excel file with 3 tables…

Hi @pedroccamara. I think it would be great to see how the fix incorrect total pattern falls down for you in your work-in-progress PBIX; that, along with an Excel mock-up of your desired outcome, might help the forum members better investigate/pursue your issue.
Greg

Hey @Greg
I need to understand the issue so i can solve later in another example. Don’t want you to solve it for me cose i will never understand how and why you did it.

I figure out the measure to get the max and the min date of an employee

Date Min =
CALCULATE (
MIN ( ‘Tab TimeSheet’[Date] ),
FILTER (
‘Tab TimeSheet’,
‘Tab TimeSheet’[EmployeeID] = ‘Tab Business Leader’[User ID]
)
)

Do you think you could help about this next measure?

T Hr Due =
CALCULATE (
( COUNT ( ‘Tab Dates’[Date] ) * 8 )
* DISTINCTCOUNT ( ‘Tab TimeSheet’[Employee ™] ),
FILTER ( ‘Tab Dates’, ‘Tab Dates’[Day Type] = “Weekday” )
)

this measure is working BUT, not the way it should. The T Hr Due (for each employee) measure should be the total of weekdays multiplied by the number of days between Date Min and Date Max above.

Can you help? Do you need anymore info, please let me know
Thanks
Pedro

Hi @pedroccamara

Based on the requirements provided, I have come up with below solution. Please check the attached PBIX file. EDNA_Solution_Employee_Working_Hours.pbix (57.9 KB)

Here, I have uploaded all 3 tables into PBIX, created Relationship b/w them and created below measure. This measure will provide Weekday Working Hours for employees for whom maxdate > maxdate of Date slicer.

WeekDay Total =
VAR EmpMaxDate =
LASTDATE ( ‘Tab TimeSheet’[Date] )
RETURN
IF (
EmpMaxDate < MAX ( ‘Tab Dates’[Date] ),
BLANK (),
CALCULATE (
SUM ( ‘Tab TimeSheet’[WorkedTime] ),
FILTER ( ALL ( ‘Tab Dates’ ), ‘Tab Dates’[Day Type] = “WeekDay” )
)
)

Do let us know if you need any further help.

Thanks
Ankit J

Hello @ankit
I’m so sorry to say but the measure is not working.
We do want the total of due work time, right?
So, let’s imagine the date filter is from 1/1 till 5/1. That’s 3 working days only, therefore 24 hours due to work.
The visual it’s suppose to show everybody who has worked on those days and what they should have worked, so, everybody with 24 in the due work measure and the total that they have worked.
Also, when i say above about the date min, there’s a new column on the Tab BusinessLeader which is Admission Date. That means, in that visual you should show only the hours due and the worked hours from that date for each employee.
Finally, the connection for the date table has to be inactive because i have to fact tables.

I really hope you can do this one. It’s very difficult for me to understand the rules and the priority when you’re writing a measure, you know?

Thanks a lot for your effort
Pedro
P.S. I’m attaching a new excel file with the admission date on the Tab BusinessLeader
TM.xlsx (25.1 KB)

Hi @pedroccamara

Thanks for the explanation. Based on this, I have modified my solution. PFA the new solution.

EDNA_Solution_Employee_Working_Hours.pbix (60.8 KB)

I have created three measures as below.
Employee Total Hours - To calculate Total Hours worked by Employee
WorkHours Due - Total Work Hours due considering minimum date from Tab TimeSheet.
WorkHours Due_BL AdmissionDate - Total Work Hours due considering Admission date from Tab BusinessLeader

Two different measures for WorkHours due as minimum date and admission date are coming as different. You can use whichever suits you.

In case this doesn’t match your expectation, will suggest to provide sample example Output in an excel.

Thanks
Ankit J

Hey @ankit
I believe you’re much closer to the real solution.
I’m not sure, maybe i didn’t mentioned, but the workhours Due have to relate to admission date.
If the dates filter are from 1 till 6 but my admission date is the 4th, we have to count from the 4th, all weekdays, right? The same for the worked time, employee total hours, we have to consider the admission date in order to sum all hours worked from the admission date of employee till the last date of the filter dates, in this case/example, the 6th.
Please feel free to add a termination date as a new column to the Tab BusinessLeader, cose in the future that’s what’s going to happen.
Please, don’t give up
Thanks a lot Ankit

Hi @pedroccamara

PFA the latest and final solution from my side. EDNA_Solution_Employee_Working_Hours.pbix (60.9 KB)

Employee Total Hours_BL - Total Hours worked considering all filtered dates from Admission Date.
Employee Total Hours_BL_WeekDays - Total Hours worked considering only Weekdays from Admission Date.
WorkHours Due_BL - Work Hours due considering Admission Date and Weekdays.

I have added one extra condition to show Blank() if admission date is blank. Remove it if not required.

I have created the Solution as per your expectation. However my suggestions will be to

  1. Create Relationships wherever possible. It will make DAX easier and faster. Number of Fact Tables should not be a Showstopper
  2. Create Measures where possible. Calculated Column shall be avoided as good practice even if data is small

Thanks
Ankit Jain

1 Like

Hello @ankit
First of all, thanks a lot for your help.
I’ve done some changes in Due Hr measure, because it didn’t sum at the end. Let me show you what i mean:
Capture2
As you can see, it sums Jan 2021 but none of the other months because, i think, the 5th employee doesn’t have a due time in those months also because he just started on Jan 2021.
Here is the measure

T Hr Due =
VAR AdmissionDate =
CALCULATE (
MIN ( ‘Tab Business Leader’[data_admissao] ),
FILTER (
‘Tab Business Leader’,
‘Tab Business Leader’[User ID] = MAX ( ‘Tab TimeSheet’[EmployeeID] )
)
)
RETURN

CALCULATE (
(COUNT ( ‘Tab Dates’[Date] ) * 8 ) * DISTINCTCOUNT( ‘Tab TimeSheet’[Employee ™]),
FILTER (
‘Tab Dates’,
‘Tab Dates’[Date] >= AdmissionDate
&& ‘Tab Dates’[Date] <= MAX ( ‘Tab Dates’[Date] )
),
‘Tab Dates’[Day Type] = “WeekDay”
)

I’ve tried to add something like ALL (Dates) or ALL (Tab BusinessLeader) and it didn’t work. I’ve tried also another measure, if this measure is blank then 0, and it didn’t work.

Can you still help me?

Hi @pedroccamara

Not sure where this new data come from as it is not in the file shared earlier. Anyways, already spent too much time on this and provided solutions based on changing requirements.

Won’t be able to spend more time on this. Hopefully someone else be able to help you or you will find your solution. Best of luck.

Thanks
Ankit Jain

Hey @ankit
It’s not about having new data but about this measure not summing if finds blank values.
I totally understand the lack of time. Don’t worry about it. Someone (or me) will come up with the solution.
But thank you anyway for all your help
Pedro

Hi @pedroccamara

Was able to spend some more time this week. For Total, there is an excellent Post Forum that you can refer.

For your solution, I have made few changes and modified measures as below.

  1. To calculated WorkHoursDue for Employee with AdmissionDate not blank. This will be dynamic based on Admission Date and Min/Max Date in Slicer.

WorkHours Due_BL = >
var AdmissionDate = max(‘Tab BusinessLeader’[Admission Date])
var MinDate = if(AdmissionDate < min(‘Tab Dates’[Date]),min(‘Tab Dates’[Date]),AdmissionDate)
var WorkHours = CALCULATE (
COUNT ( ‘Tab Dates’[Date] ) * 8,
FILTER (
‘Tab Dates’,
‘Tab Dates’[Date] >= MinDate
&& ‘Tab Dates’[Date] <= MAX ( ‘Tab Dates’[Date] )
),
‘Tab Dates’[Day Type] = “WeekDay” )
return
if(isblank(AdmissionDate),blank(),WorkHours)

  1. To Calculate for Individual Employees and Total.

WorkHours Due Final =
IF (
HASONEVALUE ( ‘Tab BusinessLeader’[Name] ),
[WorkHours Due_BL],
SUMX (
SUMMARIZE (
‘Tab BusinessLeader’,
‘Tab BusinessLeader’[Name],
@Total”, [WorkHours Due_BL]
),
[@Total] ))

Similar Measures are created for Employee Actual WorkHours also. Employee Actual WorkHours measure contains all dates from Timesheet irrespective of Weekdays.

Attached the Final Solution. May need tweak if required. EDNA_Solution_Employee_Working_Hours.pbix (66.6 KB)

Thanks
Ankit J

1 Like

Hello @ankit
Thank you very much for your solution!!!

Pedro