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

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

Re: Calcualte number of emplyes by last date

@rimgri ,

 

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

Re: Calcualte number of emplyes by last date

@rimgri ,

 

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.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 266 members 2,949 guests
Please welcome our newest community members: