cancel
Showing results for
Did you mean:
Member

## Count Firs Date occurence

Hi,

Need some help.

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

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:

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Count Firs Date occurence

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

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.
3 REPLIES 3
Community Support Team

## Re: Count Firs Date occurence

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

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.
Member

## Re: Count Firs Date occurence

Thanks, @, it works with a year.

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

Member

## Re: Count Firs Date occurence

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