Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey guys
I'm trying to build a matrix visual to show me this:
JAN FEB ...
Hours DUE DIFF Hours DUE DIFF
Employee A
Employee B
Employee C
I want to calculate for each employee the total of hours due to work. I did this measure:
My emloyees ID table has employeeID, BeginDate and EndDate (this one can be null or a specific date). If null, then it should be NOW(). And this measure won't work if the dates selected in the filter of the report are different than the BeginDate and EndDate.
I'm not sure about this but for each employee i have to calculate the number of "working days" between BeginDate and EndDate or NOW() and then, times 8 to get the hours.
I hope i was clear enough.
Thanks a lot
Pedro
Hi,
I think you should convert your Table into a 2 column table - Employee ID and Date. So if there are 5 days between the begin and end date for an employee, then 5 rows will be created for that employee. This can be done in the Query Editor. Since there will only be a single Date column, you will be able to create a many to one relationship from this Date column to the Date column of the Calendar Table.
Hello @Ashish_Mathur
My timesheet table has the employeeID and the dates column. The thing here is, for example, EmployeeID 357 that starts to work on the 5th of january and i want to know all the employees that have worked and what they were suppose to work from the 6th till the 8 of january.
What will happen is that for this particular employee, it will show, due time, 32 hours for each employee ( 3 days times 😎 and it should show, for this particular employee, 24 hours. That's the measure i need to do.
Thanks
Hi,
That should not be a problem. You can always take the unique days and min hours on each day and then add them up. To get more specific help, share some data and show the expected result.
Hello @Ashish_Mathur
I'm sorry but i can't add any file (or don't know how)
The purpose of this is to have 2 measures: Hours Due and Hours Worked
Let's imagine a report that has a date filter, from 1st Jan till 4th of jan. This means only 16 hours due to work, beacause we only have 2 workdays.
About the hours worked, you have to consider the admission date of each employee, that means you only count the days from that date and on, and then times 8.
Finally, the connection between any table and the date table has to be inactive because i have to fact tables.
Let me know if there's a way of attaching a file
Thanks a lot @Ashish_Mathur for all your effort
Pedro
Hi,
Upload the file to OneDrive and share the download link here.
Here it is
https://1drv.ms/u/s!ArcWLnLtPYpTlwgvxnHep7JCzb9Q?e=eFhXxy
Thanks a lot @Ashish_Mathur
Hi,
I have read your original post and also studied the Excel file that you have uploaded. Unfortunately, I do not understand your requirement.
One way in which i can help is by studying your Excel formulas in the Excel file and then try to replicate them in the DAX language. So is it possible for your to write formulas in your MS Excel file (as though you were trying to solve this question in MS Excel and not in PBI Desktop) so that i can understand your logic.
Hello @Ashish_Mathur
Don't know how to write those 2 formulas in excel, but i can tell you what i need in a real example. The report that i need is the above, first post, for a chosen periode. Let's narrow the periode and choose an employee.
Let's imagine this scenario: Tab Dates = From 1st jan till 6th jan and employeeID 362.
T Hr Due = count the days that are weekdays (Tab Dates) between the admission date (Tab Business Leader) and the last date on the filter (6th Jan) = 3 days, therefore 24 hours
T Hr Wrkd = you have to sum all the hours between the admission date and the last date on the filter (6th Jan) = 16 hours. You have to ignore all the hours before and after the selected period.
One last thing: the connection to Tab Dates, gotta be inactive cose i have 2 more fact tables.
Maybe you're right about making another table, i don't know. But i wouldn't know how.
Thanks a lot Ashish
keep in mind that measures are computed for each element of your visual (cells and totals). That means your employee filter is already applied. But you need to add the logic for BeginDate, EndDate, and TODAY() to calculate the number of workdays that you then need to multiply by 8.
Here are the scenarios:
BeginDate > TODAY() : 0 hours
BeginDate <= TODAY <= EndDate : get countrows of workdays from your calendar table with the Begin and End filters ( DATESBETWEEN() for example), multiply by 8
TODAY>EndDate : 0 hours
Hello @lbendlin
I believe you're right but don't know how to put it in a measure. My measure ..