Forum Discussion
Anonymous
7 years agoNot applicable
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
Anonymous,
You may refer to the post below.
1 Reply
- v-chuncz-msftCommunity Support
Anonymous,
You may refer to the post below.