Weekdays between 2 dates on dim employee

[pedroccamara]

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

[Melissa]

Hi @pedroccamara,

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

Time Intelligence series by BrianJ and Melissa

Hi everyone, We’ve brought the Time Intelligence series we did for Enterprise DNA TV together in this post so it’ll be easier to find and navigate through completely. @BrianJ and I had great fun doing these. If you have any questions or concerns about the content covered in these video’s please create a new topic, refer to the video and @mention BrianJ or Melissa. But we’d also welcome your feedback within this thread of course. – THANK YOU – How to create a Holiday table [Ho…

[pedroccamara]

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?

[Melissa]

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.

[pedroccamara]

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?

[Melissa]

Hi @pedroccamara,

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

[AntrikshSharma]

@pedroccamara

Also, it seems this measure didn’ care about my dates filter

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.

[pedroccamara]

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',
            'Tab TimeSheet'[EmployeeID] = RELATED ( 'Tab Business Leader'[User ID] )
        )
    )
VAR _DateMax =
    CALCULATE (
        MAX ( 'Tab TimeSheet'[Date] ),
        FILTER (
            'Tab TimeSheet',
            'Tab TimeSheet'[EmployeeID] = RELATED ( 'Tab Business Leader'[User ID] )
        )
    )
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?

[AntrikshSharma]

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

[pedroccamara]

@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?

[Greg]

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

[pedroccamara]

Thank you for the reminder @Greg

[pedroccamara]

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

[pedroccamara]

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