Count Firs Date occurence

[Anonymous]

Hi,

Need some help.

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

I had three tables:

 Calendar table:

Date	Year	Month	Year_month
2012-01-01	2012	saus	201201
2012-01-02	2012	saus	201201
2012-01-03	2012	saus	201201
2012-01-04	2012	saus	201201
2012-01-05	2012	saus	201201
2012-01-06	2012	saus	201201
2012-01-07	2012	saus	201201
2012-01-08	2012	saus	201201

Assignment table

KEY1	Emp_no	Ass_No	Start_date	End_date
153_17	153	17	2019-01-01
177_6	177	6	2019-02-01
199_6	199	6	2019-02-01
433_7	433	7	2019-01-01
528_10	528	10	2019-01-01
677_16	677	16	2019-01-01
691_18	691	18	2019-02-01
695_12	695	12	2019-01-01
733_9	733	9	2019-02-01
1919_8	1919	8	2019-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:

https://drive.google.com/file/d/1XBVlZA4z_831sMV86OPhkxz3jGdtzDd5/view?usp=sharing

[v-chuncz-msft · Accepted solution]

@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]

Thanks, @v-chuncz-msft, it works with a year.

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

[Anonymous]

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] ) )
                )
            )
        )
    )
)