Cross Join Table With End Dates

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.

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))