Cross Tables

[imy]

Hi,

I have created a crossjoin table to see how many employees are active per year in our company:

Crosstable Emp/year2 = FILTER(CROSSJOIN('All Employee Details','Date'),'Date'[Date]>='All Employee Details'[Contract Start Date]&&'Date'[Date]<='All Employee Details'[Contract End Date]).

However, when I create the bar chart is not taking into consideration the permanent employees as they dont have a Contract End Date (is blank)

How can I go around this?

Many thanks in advance for your help!

Imy

[v-lili6-msft]

hi @imy 

Just try this formula:

Crosstable Emp/year2 = 
FILTER (
    CROSSJOIN ( 'All Employee Details', 'Date' ),
    'Date'[Date] >= 'All Employee Details'[Contract Start Date]
       -- && 'Date'[Date] <= 'All Employee Details'[Contract End Date]
&& 'Date'[Date] <= IF(ISBLANK('All Employee Details'[Contract End Date]),TODAY(),'All Employee Details'[Contract End Date])
)

or

Crosstable Emp/year2 = 
FILTER (
    CROSSJOIN ( 'All Employee Details', 'Date' ),
    'Date'[Date] >= 'All Employee Details'[Contract Start Date]
       -- && 'Date'[Date] <= 'All Employee Details'[Contract End Date]

       &&'Date'[Date] <= IF(ISBLANK('All Employee Details'[Contract End Date]),MAX('Date'[Date]),'All Employee Details'[Contract End Date])
)

Regards,

Lin

[imy]

Dear Lin,

I used this formula as I think is a great idea to put all permanent staff with end date today (this way they are taken into consideration in my bar chart);

Crosstable Emp/year2 = 
FILTER (
    CROSSJOIN ( 'All Employee Details', 'Date' ),
    'Date'[Date] >= 'All Employee Details'[Contract Start Date]
       -- && 'Date'[Date] <= 'All Employee Details'[Contract End Date]
&& 'Date'[Date] <= IF(ISBLANK('All Employee Details'[Contract End Date]),TODAY(),'All Employee Details'[Contract End Date])
)

Crosstable Emp/year2 = FILTER ( CROSSJOIN ( 'All Employee Details', 'Date' ), 'Date'[Date] >= 'All Employee Details'[Contract Start Date] -- && 'Date'[Date] <= 'All Employee Details'[Contract End Date] && 'Date'[Date] <= IF(ISBLANK('All Employee Details'[Contract End Date]),TODAY(),'All Employee Details'[Contract End Date]) )

However, The Contract End Date is still empty, why is this happening?

Many thanks

Imy

[v-lili6-msft]

hi @imy 

It should work well, try to remove these code in the formula

-- && 'Date'[Date] <= 'All Employee Details'[Contract End Date]

Crosstable Emp/year2 =
FILTER (
    CROSSJOIN ( 'All Employee Details', 'Date' ),
    'Date'[Date] >= 'All Employee Details'[Contract Start Date]
        && 'Date'[Date]
            <= IF (
                ISBLANK ( 'All Employee Details'[Contract End Date] ),
                TODAY (),
                'All Employee Details'[Contract End Date]
            )
)

For example:

Result:

Do you mean that you want the Contract End Date column for ID 2 is not blank? 

Regards,

Lin

[VasTg]

@imy 

Try this.

FILTER(CROSSJOIN('All Employee Details','Date'),'Date'[Date]>='All Employee Details'['Contract Start Date']&&'Date'[Date]<='All Employee Details'['Contract End Date']||('Date'[Date]>='All Employee Details'['Contract Start Date']&&ISBLANK('All Employee Details'['Contract End Date'])))

If it works, hit the answered button.