DAX Help Calculating Distinct Count while applying Date Filters on each row

[PossibleGuru123]

Hello,

I am trying to calculate total hours possible (expected work hours; 36.9 hours per week per person). The trick is this: I need to include only active employees (the first filter) and I need to ignore weeks in between where they are on extended leave (the second filter). This works just fine when viewing hours possible per person, but it does not work when viewing the top level Team (when viewing in a Matrix where you can expand Team --> Employee). Any suggestions please? I tried to use a summary table that calculates hours possible per week per person, but it was not working out well either. I was thinking of using COUNTX or SUMX somehow.

    CALCULATE(

        CALCULATE(

        DISTINCTCOUNT('Teams'[Name]),

        FILTER(

            Teams,

            Teams[Start Week] <= MIN( 'Calendar'[WeekStarting] ) &&

                Teams[End Week] >= MAX( 'Calendar'[WeekStarting] )

            )),

        FILTER(

            'Calendar',

            'Calendar'[WeekStarting] < MIN(ExtLeavers[StartDate]) ||

            'Calendar'[WeekStarting] > MAX(ExtLeavers[EndDate]))

        ) * (36.9 * [_WeeksInRange])

You can see here the team total should be around 200 less than 1,328. Since there is no filter on the employee name, the extended leavers date range filter isn't calculating per employee. I want to calculate the hours per person first with the filters applied, and then add that up.

[PossibleGuru123 · Accepted solution]

Thanks for the replies. When viewing the total per person, the filters are working correctly. User A should be 36.9 every week, while User B should be 0 for every week in December. I want the grant total to calculate the hours per user and then add the hours up. The row totals should be 36.9 for those weeks, but instead they are 0 or 73.8. The logic I am trying to do is something like a SUMX, where the hours is calculated per row first and then added together.

I think I have resolved the issue. I used a calculated table to first calculate the hours possible per user, per week, that met the 2 filter conditions I needed. That game me _Summary table with WeekStarting, user_email, and possible. I then used a SUMX measure to add the total hours possible. Woohoo! Hopefully this helps someone out one day. The data is setup so that I have a calendar table and timecard data, where not every person would have timecard hours submitted every single day. I then needed to calculate how many hours we expected that person/team to have submitted hours (hours possible). I also couldn't rely solely on the calculated summary table, because I could not build table relationships with the other tables due to circular dependencies.

_Summary (Table) =

    SUMMARIZECOLUMNS(

        'Calendar'[WeekStarting], Teams[Email],

        "Possible",

            var _pos =

                CALCULATE(

                    CALCULATE(

                    DISTINCTCOUNT('Teams'[Name]),

                    FILTER(

                        Teams,

                        Teams[Start Week] <= MIN( 'Calendar'[WeekStarting] ) &&

                            Teams[End Week] >= MAX( 'Calendar'[WeekStarting] )

                        )),

                    FILTER(

                        'Calendar',

                        'Calendar'[WeekStarting] < MIN(ExtLeavers[StartDate]) ||

                        'Calendar'[WeekStarting] > MAX(ExtLeavers[EndDate]))

                    ) * 36.9

            var _results = IF(_pos > 0, _pos, 0)

            RETURN

            IF( _results < 0, 0, _results )

    )

Hours Possible =

    CALCULATE(

        SUM(_Summary[Possible]),

        _Summary[Email] IN VALUES(Teams[Email]),

        _Summary[WeekStarting] IN VALUES('Calendar'[WeekStarting])

    )

You can see now that no matter what the context is, the hours is being calculated correctly. Before, the totals for the selected weeks was 0 in this table (wrong) or in another context, it was 73.80 (also wrong).

[v-rzhou-msft]

Hi @PossibleGuru123 ,

According to your statement, I think your requirement is to filter the employees who are still active.

However I think your calculation is based on your data model. I am confused about how to get result 200 based on your screenshot.

You can check amitchandak's answer, if this couldn't resolve your issue, please share a sample file with us and show us a screenshot with the result you want.

This will make us easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PossibleGuru123 , use a disconnected date table and use a measure of active/current employees as provided in the file

Current Employees = CALCULATE(DISTINCTCOUNT(Employee[Employee Id]),FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))))