cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rimgri Member
Member

Count Firs Date occurence

Hi,

Need some help.

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

I had three tables:

 Calendar table:

DateYearMonthYear_month
2012-01-012012saus201201
2012-01-022012saus201201
2012-01-032012saus201201
2012-01-042012saus201201
2012-01-052012saus201201
2012-01-062012saus201201
2012-01-072012saus201201
2012-01-082012saus201201

Assignment table

KEY1Emp_noAss_NoStart_dateEnd_date
153_17153172019-01-01 
177_617762019-02-01 
199_619962019-02-01 
433_743372019-01-01 
528_10528102019-01-01 
677_16677162019-01-01 
691_18691182019-02-01 
695_12695122019-01-01 
733_973392019-02-01 
1919_8191982019-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
Screenshot_46.png
And what it should look like for year:
 
Screenshot_45.png
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
Community Support Team

Re: Count Firs Date occurence

@rimgri ,

 

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
Community Support Team

Re: Count Firs Date occurence

@rimgri ,

 

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.
rimgri Member
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)?

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