## Desktop

Super Contributor
Posts: 1,370
Registered: ‎07-27-2016

# help with filter statement in DAX

i need to calculate a moving average over time, i need to get the no of employees at a point in time.

i have a start date and i have an employee end date, how do i calculate the moving average over time, taking into consideration i am plotting on 2 different dates for the above?

currently i have this but it doesn't seem to work

No of Current Employees = CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),
FILTER('Employees All Time',
min('Date'[Date]) >= 'Employees All Time'[Start Date]
&& MAX('Date'[Date]) < 'Employees All Time'[Leaving Date] )
)

this should give me a total at a day level.

however i am also stuck as to how to calculate the average.

it doesn't return anything which is quite frustrasting help please

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: help with filter statement in DAX

Hi @vanessafvg,

Could you try the formula below to see if it works in your scenario?

```No of Current Employees =
VAR currentDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Employees All Time'[Employee No.] ),
FILTER (
ALL ( 'Employees All Time' ),
'Employees All Time'[Start Date] <= currentDate
&& 'Employees All Time'[Leaving Date] > currentDate
)
)
```

Regards

Super Contributor
Posts: 1,370
Registered: ‎07-27-2016

## Re: help with filter statement in DAX

thanks will give it a bash

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: help with filter statement in DAX

Hi @vanessafvg,

Have you tried the solution? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

Regards

Super Contributor
Posts: 1,370
Registered: ‎07-27-2016

## Re: help with filter statement in DAX

[ Edited ]

hi @v-ljerr-msft i have been working on something else, so can't say if its the solution yet sorry.

Community Support Team
Posts: 3,697
Registered: ‎07-17-2016

## Re: help with filter statement in DAX

Hi @vanessafvg,

That's OK. Just take your time.

Regards

Super Contributor
Posts: 1,370
Registered: ‎07-27-2016