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.
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)
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
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?
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
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” )
)
)
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)
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.
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
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
Create Relationships wherever possible. It will make DAX easier and faster. Number of Fact Tables should not be a Showstopper
Create Measures where possible. Calculated Column shall be avoided as good practice even if data is small
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:
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
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.
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.
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
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.
