cancel
Showing results for
Did you mean:
Highlighted
Member

## 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 Team

## Re: Calcualte number of emplyes by last date

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 Team

## Re: Calcualte number of emplyes by last date

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

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 266 members 2,949 guests
Recent signins: