Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating CROSSJOIN using a filtered date column

I'm trying to calculate the average number of active daily employees across specific time periods. So I'm trying building a CROSSJOIN virtual table that can return only the dates that each employee are active, iterated off of each EmployeeID, which I can use to count the number of employees working each day.

 

 Employee Table

EmployeeID              Hire Date     Termination Date
1                       7/1/2017      7/4/2017
2                       10/1/2017     NULL
3                       9/15/2017     12/1/2017       
4                       1/5/2018      NULL

Date Table

 

Dates
7/1/2017
7/2/2017
7/3/2017
7/4/2017
...

 

 

OUTPUT:

EmployeeActiveDays

 

EmployeeID     Active Dates     
1 7/1/2017
1 7/2/2017
1 7/3/2017
1 7/4/2017
2 10/1/2017
2 10/2/2017
...

 

I wrote out this CROSSJOIN, but keep getting an error that I need some sort of aggregation within the DATESBETWEEN expression. How would I be able to search on Hire & Terminate Dates for each employee, rather than a min/max, for example, of each column if I added an aggregation?

 

EmployeeActiveDays: 
=CROSSJOIN(
VALUES( Employee Table[EmployeeID],
DATESBETWEEN( Date Table[Dates],
Employee Table[Hire Date],
Employee Table[Terminate Date]
)
)
)

 

Thanks