Forum Discussion

imy's avatar
imy
Icon for Helper I rankHelper I
6 years ago

Cross Tables

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 Empl...
Need Help
imy's avatar
imy
Icon for Helper I rankHelper I
6 years ago

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's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

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