Due working days, DATEDIFF?

[pedroccamaraDBI]

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:

T Hr Due =

CALCULATE (

(COUNT ( 'Tab Dates'[Date] ) * 😎 * DISTINCTCOUNT( 'Tab TimeSheet'[Employee Name (TM)]),

FILTER ( 'Tab Dates', 'Tab Dates'[Day Type] = "Weekday" )

)

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

[Ashish_Mathur]

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.

[pedroccamaraDBI]

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

[Ashish_Mathur]

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.

[pedroccamaraDBI]

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

[Ashish_Mathur]

Hi,

Upload the file to OneDrive and share the download link here.

[pedroccamaraDBI]

Here it is

https://1drv.ms/u/s!ArcWLnLtPYpTlwgvxnHep7JCzb9Q?e=eFhXxy

Thanks a lot @Ashish_Mathur 

[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.

[pedroccamaraDBI]

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

[lbendlin]

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

[pedroccamaraDBI]

Hello @lbendlin 
I believe you're right but don't know how to put it in a measure. My measure ..

T Hr Due =

CALCULATE (

(COUNT ( 'Tab Dates'[Date] ) * 8 ) * DISTINCTCOUNT( 'Tab TimeSheet'[Employee Name (TM)]),

FILTER ( 'Tab Dates', 'Tab Dates'[Day Type] = "Weekday" )

)

I think, but don't know how, i should replace this part of my measure

(COUNT ( 'Tab Dates'[Date] )

with something that shows the difference between the admitionDate of the employee table and now().
Don't you think?
Thanks for your help