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 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
 
Need Help

4 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    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.

     

  • 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