# Weekdays between 2 dates on dim employee

Hello all
I need to create a measure that calculates the working hours between 2 dates on a dim table.
3 tables : Tab Dates, Tab TimeSheet and Tab Business Leader. The first 2 are connected through dates but with inactive connection because i have 2 other fact table.
This is the desired visual (all numbers here has nothing to do with the file attached)

For the Hours worked for each employee i have this measure:

Ts Hours =
CALCULATE (
SUM ( ‘Tab TimeSheet’[Time (h)] ),
USERELATIONSHIP ( ‘Tab Dates’[Date], ‘Tab TimeSheet’[Date] )
)

Now, what i need is a measure, that shows me the total of hours due to work between date min and date max on the Tab Business Leader, for each employee.

I’m attaching a file with those 3 tables.
TM Last.xlsx (17.5 KB)

Thanks a lot everyone
Pedro

Think you’ll find all you need here, in the video by @BrianJ

2 Likes

Hey @Melissa
Thanks a lot for the answer.
I’ve already seen this video and i couldn’t use it because what i said above about the relationship between dates table and the dimension table but also that fact table. But that’s my guess only.
I’ve found another video from Marco Russo https://www.youtube.com/watch?v=JgeUhXkxXbU&ab_channel=GuyinaCube but also it didn’t work for me.
Meanwhile i’ve tried that approach and it shows me this result:

Date Min and Date Max are the dates between any employee should enter their time for each project.
Any idea why isn’t working?

Please read the error message… you are not returning a single (scalar) value for the second and third parameter of DATESBETWEEN. To solve that create two VARs to determine a Min and Max date value and then after the RETURN statement refer to them in the DATESBETWEEN function.

Some other things to keep in mind.

• If StartDate is a blank date value, then StartDate will be the earliest value in the dates column.
• If EndDate is a blank date value, then EndDate will be the latest value in the dates column.
• The dates used as the StartDate and EndDate are inclusive.
• If StartDate is larger than EndDate, the result is an empty table.
1 Like

Hey @Melissa
I believe we’re on right track.

But because of what you said, i confirm that there’s blanks on my datemin as well on my datemax. Also, it seems this measure didn’ care about my dates filter. It shows the values according my datemin and datemax.
Can you still help me?

Please provide a PBIX mock up with the work you have done so far.

That’s because ALLSELECTED isn’t only removing filters from ‘Tab Business Leader’ but also from tables related to it with Many to 1 Relationship. Use VALUES ( Dates ) to restore filters over dates table.

3 Likes

Hello @AntrikshSharma and @Melissa
You guys are the best. I believe now this measure is finally working…almost!
There’s a tiny little problem about the total. If my visual is the above on my original post, it’s suppose to give all the hours at the total and not 56 which is what it is showing . I’ve change that measure to this, according your suggestion:

``````T Hr Due =
VAR _DateMin =
CALCULATE (
MIN ( 'Tab TimeSheet'[Date] ),
FILTER (
'Tab TimeSheet',
)
)
VAR _DateMax =
CALCULATE (
MAX ( 'Tab TimeSheet'[Date] ),
FILTER (
'Tab TimeSheet',
)
)
RETURN
CALCULATE (
COUNTROWS ( 'Tab Dates' ) * 8,
DATESBETWEEN ( 'Tab Dates'[Date], _DateMin, _DateMax ),
'Tab Dates'[Day Type] = "Weekday",
VALUES ( 'Tab Dates' )
)
``````

This is the way you’ve suggested right?

@pedroccamara Can’t tell, there are few things incorrect here, that FILTER inside CALCULATE is not useful unless you were trying to do something based on expanded table(advanced topic), and Min and Max would be returning the same value.

@AntrikshSharma those VAR are calculating the min date and the max date for each employee id on the fact table, tab timesheet. Do you think both measures are wrong? or …would you do it other way?

Hi @pedroccamara. Please use the built-in DAX formatter when posting DAX code … it will make things easier for all to read.
Greg

1 Like

Thank you for the reminder @Greg

Hey @AntrikshSharma
I know you have little time to help here. I understand that. And it’s totally fine if you can’t answer this.
I’ve change the measure above to a simpler one and it gave me the same result, i mean, the due hours for each employee is correct but doesn’t give the total. It seems the due total is for the date filter only, for example from 5 till 8 (3 days), shows me 24 and of course not all the employees. The measure is this :

``````    T Hr Due  =
CALCULATE (
[DueWorkTime],
DATESBETWEEN ( 'Tab Dates'[Date], [DateMin], [DateMax]),
VALUES( 'Tab Dates' )
)
``````

The first measure

``````        DueWorkTime = SUM ( 'Tab Dates'[DueWorkTime] )
``````

This measure is summing only the hours of weekdays on my calendar

and the last one

``````DateMax =
CALCULATE (
MAX ( 'Tab TimeSheet'[Date] ),
FILTER (
'Tab TimeSheet',
'Tab TimeSheet'[id_user] = RELATED ( 'Tab Employee'[id_user] )
))
``````

the same measure above for DateMin but with MIN instead of MAX

Any help to understand would be much appreciated
Thanks a lot

Hello @Melissa and @AntrikshSharma
Thank you so much for all your help and all your attempts on doing so.

The formula works like this:

``````    T Hr Due =
SUMX(
VALUES( 'Tab Employee'[id_user] ),
CALCULATE(
CALCULATE(
[DueWorkTime],
DATESBETWEEN(
'Tab Dates'[Date],
[Employee DateMin],
[Employee DateMax]
),
VALUES( 'Tab Dates' )
)
)
)

Thank you all
Pedro``````