cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
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.

View solution in original post

1 REPLY 1
Community Support
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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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