Forum Discussion

imy's avatar
imy
Helper 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
v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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's avatar
imy
Helper 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
    Community 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