Calculating CROSSJOIN using a filtered date column

[Anonymous]

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

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the post below.

https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to-date/td-p/551491