Calcualte number of emplyes by last date

[Anonymous]

Hi,

I have stuck with calculating how much employees were fired by time.

I have 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). SQL returns date 1753-01-01 if the date is Blank, so I calculated new column [Fired date] which takes if the End date is 1753-01-01 (blank), then return MAX calendar date.

AssmxDate = SELECTCOLUMNS( FILTER( CROSSJOIN(Assignment;'Calendar'); FIRSTDATE(Assignment[Start_date])<='Calendar'[Date]&& LASTDATE(Assignment[Fired Date])>='Calendar'[Date]); "Date"; 'Calendar'[Date]; "KEY1"; Assignment[KEY1]; "Employee"; Assignment[Emp_no] )

I want to calculate the number of employees was fired according to date. I tried to write DAX, but it returns a blank. It should be 2018 - 2 people and in 2019 - 24 people

Lost = 
VAR minDate = MIN ( 'Calendar'[Date])
VAR maxDate = MAX ('Calendar'[Date])
VAR Last= IF(LASTDATE(AssmxDate[Date])=MAX('Calendar'[Date]); BLANK();LASTDATE(AssmxDate[Date]))
RETURN
   CALCULATE (
      DISTINCTCOUNT(AssmxDate[Employee]);
      FILTER (
         'Calendar';
         ( Last >= minDate && Last <= maxDate )
      )
   )

Can anyone help me to write correct DAX?

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 (
                    NOT ( CONTAINS ( Assignment, Assignment[End_date], DATE ( 1753, 1, 1 ) ) )
                        && YEAR ( LASTDATE ( Assignment[End_date] ) )
                            = VALUE ( SELECTEDVALUE ( 'Calendar'[Year] ) )
                )
            )
        )
    )
)