CODEWITHSUNDEEP
dax
janvanwerkhoven
janvanwerkhoven

Reputation: 238

Aggregate a PREVIOUSMONTH measure using SUMX when table has no row in current month

I am trying to build an overview of employees that are in service per month, the number of employees that went into service and the number of employees that left that month.

I have monthly overviews of all employees that are in service, which looks like this:

+----------+----------+------------+
|   date   | Employee | Department |
+----------+----------+------------+
| 1/1/2019 | A        | Sales      |
| 1/1/2019 | D        | Projects   |
| 2/1/2019 | A        | Sales      |
| 2/1/2019 | B        | Sales      |
| 2/1/2019 | C        | Marketing  |
| 2/1/2019 | D        | Projects   |
| 3/1/2019 | A        | Marketing  |
| 3/1/2019 | B        | Sales      |
| 3/1/2019 | C        | Marketing  |
| 3/1/2019 | D        | Projects   |
| 4/1/2019 | A        | Marketing  |
| 4/1/2019 | B        | Sales      |
+----------+----------+------------+

To calculate the number of active employees in a month I use the measure:

# Employees = COUNTROWS(Employees)

The idea is to use the PREVIOUSMONTH expression to find the number of employees last month and compare it with this month:

# Employees Last Month = CALCULATE([# Employees],PREVIOUSMONTH(DateTable[Date]))

The net inflow/outflow per month is then easily calculated using:

Inflow/Outflow = [# Employees] - [# Employees Last Month]

throwing these in a table per employee gives me the overview that I expect:

enter image description here

I would now want to sum per month all of the of the employees with inflow/outflow = 1 as the inflow and the employees with inflow/outflow = -1 as the outflow. This is where I am running into problems.

My inflow measure seems to work fine:

Inflow = 
SUMX (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( DateTable[MonthSort] ), VALUES ( Employees[Employee] ) ),
            "IO", [Inflow/Outflow]
        ),
        [IO] = 1
    ),
    [IO]
)

but the outflow measure does not return anything:

Outflow = 
SUMX (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( DateTable[MonthSort] ), VALUES ( Employees[Employee] ) ),
            "IO", [Inflow/Outflow]
        ),
        [IO] = -1
    ),
    [IO]
)

it seems that in the context of an employee that does not have data in a specific month the VALUES(Employee[Employee]) expression returns nothing (using the test measure) which might explain the problem:

TestEmployee = MAX(Employee[Employee])

enter image description here

Is there anything I am missing? as a calculated table the expressions seem to work fine.

Upvotes: 2

Views: 636

Answers (1)

greggyb
greggyb

Reputation: 3798

Introduce an employee dimension, with just the attributes of the unique employees (E.g. their ID, their name - anything that is not changing about them). Very explicitly, that dimension should not have department, because department is changing over time for employees.

In =
COUNTROWS (
    FILTER (
        VALUES ( 'DimEmployee'[Employee] ),
        [Inflow/Outflow] = 1
    )
)

Out =
COUNTROWS (
    FILTER (
        ALL ( 'DimEmployee'[Employee] ),
        [Inflow/Outflow] = -1
    )
)

You were spot on about the employee not existing in the context of a specific date. Since 'DimEmployee' is date-context-free, this doesn't matter.

Upvotes: 1

Related Questions