Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Firs Date occurence

Hi,

Need some help.

I want to calculate how much new employees [Emp_no] started on that day.

I had three tables:

 Calendar table:

DateYearMonthYear_month
2012-01-012012saus201201
2012-01-022012saus201201
2012-01-032012saus201201
2012-01-042012saus201201
2012-01-052012saus201201
2012-01-062012saus201201
2012-01-072012saus201201
2012-01-082012saus201201

Assignment table

KEY1Emp_noAss_NoStart_dateEnd_date
153_17153172019-01-01 
177_617762019-02-01 
199_619962019-02-01 
433_743372019-01-01 
528_10528102019-01-01 
677_16677162019-01-01 
691_18691182019-02-01 
695_12695122019-01-01 
733_973392019-02-01 
1919_8191982019-02-01 

And calculated the third table which is a combination of previous two (Empl_no split by Start date and End date) If end date 1753-01-01 then blank.

AssmxDate = 
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(Assignment;'Calendar');
        FIRSTDATE(Assignment[Start_date])<='Calendar'[Date]&&
        LASTDATE(Assignment[End_date])>='Calendar'[Date]);
        "Date"; 'Calendar'[Date];
        "KEY1"; Assignment[KEY1];
        "Employee"; Assignment[Emp_no]
        )
I want to calculate how much new employees started on a particular period (month/year/day).
I'm not very good at complicated DAX, so tried to write a simple one:
New = 
CALCULATE(DISTINCTCOUNT(Assignment[Emp_no]);
FILTER('Calendar';FIRSTDATE(Assignment[Start_date])='Calendar'[Date]))
but it brings all new employees for all calendar
And what it should look like for year:
 
Can you help me to get the correct number of new employees?
This is an example file:
 
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous ,

     

    You may refer to the measure below.

     

    Measure =
SUMX (
    VALUES ( 'Calendar'[Year] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                VALUES ( Assignment[Emp_no] ),
                CALCULATE (
                    YEAR ( FIRSTDATE ( Assignment[Start_date] ) )
                        = VALUE ( SELECTEDVALUE ( 'Calendar'[Year] ) )
                )
            )
        )
    )
)

     

     

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    You may refer to the measure below.

     

    Measure =
SUMX (
    VALUES ( 'Calendar'[Year] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                VALUES ( Assignment[Emp_no] ),
                CALCULATE (
                    YEAR ( FIRSTDATE ( Assignment[Start_date] ) )
                        = VALUE ( SELECTEDVALUE ( 'Calendar'[Year] ) )
                )
            )
        )
    )
)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, @, it works with a year.

      What changes must be done, if I want to make this measure dynamic (calculate for year/month/day)?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I got it for dynamic calendar

        Measure =
SUMX (
    VALUES ( 'Calendar'[Date] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                VALUES ( Assignment[Emp_no] ),
                CALCULATE (
                    FIRSTDATE ( Assignment[Start_date] ) 
                        = VALUE ( SELECTEDVALUE ( 'Calendar'[Date] ) )
                )
            )
        )
    )
)