Measure to find the active Employees based on Salary credit

[Ramees_123]

I have the below table related to employees salary.

Employee ID

Salary Credited

Salary Date

I also have a Date dimension table from which I give year and month slicers in report. Now I need to find the count of active employees ID with a fomula whether they have received salary in last 3 months or not ?

If they have received salary for all the last three months, then they are active otherwise inactive.

How to create this measure. Please help.

[v-eqin-msft · Accepted solution]

Hi @Ramees_123 ,

Please follow these steps:

1. Add a YearMonth column to Calendar table which will used for slicer:

=YEAR([Date])*100+MONTH([Date])

2.calculate the count of records when in the past 3 month based on the selected YearMonth in slicer:

Flag =
VAR _maxDate =
    MAX ( 'Calendar'[YearMonth] )
VAR _minDate =
    EOMONTH ( _maxDate, -3 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Employee ID] = MAX ( 'Table'[Employee ID] )
                && YEAR ( [Salary Date] ) * 100
                    + MONTH ( [Salary Date] ) > _minDate
                && YEAR ( [Salary Date] ) * 100
                    + MONTH ( [Salary Date] ) <= _maxDate
        )
    )+0

3.Then count Flag>=3:

Count of active employees = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),FILTER('Table',[Flag]>=3))+0

The final output is shown below:

 Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ramees_123 , with help from date table

Rolling 3 = CALCULATE(distinctcount(Table[Employee ID]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

or

Rolling 3 = CALCULATE(distinctcount(Table[Employee ID]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-3,MONTH))

or

Rolling 3 = CALCULATE(distinctcount(Table[Employee ID]),DATESINPERIOD('Date'[Date ],today(),-3,MONTH))

[Ramees_123]

@amitchandak  Hi Amit, I don't have any relationship between date table and this salary table due to some reasons.

Will your DAX works if there is no relationships ?

[amitchandak]

@Ramees_123 , not it needs a continuous date that might not work if you use tbale

like

Rolling 3 = CALCULATE(distinctcount(Table[Employee ID]),DATESINPERIOD('Table'[Date ],MAX('Table'[Date ]),-3,MONTH))

Try without TI and Date

Rolling 3 = CALCULATE(distinctcount(Table[Employee ID]),filter(all(Table[Date]), Table[Date] <= max(Table[Date]) && Table[Date] >= eomonth(max(Table[Date]),-3)))