Cross Join Table With End Dates

[Pink1623]

I am trying to create a table for budgeting that joins the staff table to a date table, with the intention of ending up with 1 row for each day and each staff member like so :

01/07/2019	M.Mouse
02/07/2019	M.Mouse
03/07/2019	M.Mouse
04/07/2019	M.Mouse
01/07/2019	D.Duck
02/07/2019	D.Duck
03/07/2019	D.Duck
04/07/2019	D.Duck

I can do this easily by creating a table with a cross join 

Daily Targets = crossjoin(values('Calendar'[Date]),VALUES('Staff'[Staff ID]))

However my staff table has start and finish dates and I want my Daily Targets table to only create records for the dates that each person was employed. So if M. Mouse leaves on 02/07/2019 The example becomes

01/07/2019	M.Mouse
02/07/2019	M.Mouse
01/07/2019	D.Duck
02/07/2019	D.Duck
03/07/2019	D.Duck
04/07/2019	D.Duck

Is this possible?

[slanka · Accepted solution]

Hi,

There is no direct way of filter the rows when you are joining the tables using CROSSJOIN. Instead, after doing a crossjoin, you can create a calculated column that will give status to each row and you can pull ONLY on the dates when employee have start date and end date by using report level filter IsActive = 1.

[danextian · Accepted solution]

Hi @Pink1623 ,

Have you tried using Power Query instead? You can create a custom column to generate a list of dates starting from employment start to end and if end is null then use today's date. After creating a column of lists, you can expand it to new rows. Here's a sample code and some screenshots

let 
end  = if [End] = null then Date.From(DateTime.LocalNow())
else [End],
duration = Duration.Days(end - [Start]) + 1
in
List.Dates([Start], duration, #duration(1,0,0,0))

[Pink1623]

Thanks for your help

[slanka · Accepted solution]

Hi,

There is no direct way of filter the rows when you are joining the tables using CROSSJOIN. Instead, after doing a crossjoin, you can create a calculated column that will give status to each row and you can pull ONLY on the dates when employee have start date and end date by using report level filter IsActive = 1.

[Pink1623]

Thanks Slanka, good to know