cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calcualte number of emplyes by last date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Calcualte number of emplyes by last date

@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] ) )
)
)
)
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

## Re: Calcualte number of emplyes by last date

@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] ) )
)
)
)
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors