Rolling 12 Month Counts

[Anonymous]

I need to calculate rolling average in a little different way:

Step1 Get the active employee counts 12 month back i.e; 6/1/2019 counts as of today 

Step 2 Get active employee counts previous month i.e, 5/1/2020 counts as of today

Step 3 Add then get average of those 2 counts.

The date sample and example: Counts of Previous Month(5/1/2020) is 2 and counts of 12 Month back(6/1/2019) is 3.

the average is 2.5

DateRan	ID	Location
5/1/2020	101	ABC
5/1/2020	102	DEF
4/1/2020	101	ABC
4/1/2020	102	DEF
6/1/2019	101	AAA
6/1//2019	103	WWW
6/1/2019	105	EEE

The date is captured every first of the month and I have data every month

Here is formula I tried

Count of Active Employee =

VAR endOfPeriod =EOMONTH(TODAY(),-13)+1

VAR startOfPeriod = EOMONTH(TODAY(),-2)+1

RETURN

CALCULATE (

COUNTROWS('ActiveEmp'), FILTER(ALL('ActiveEmp'), ('ActiveEmp'[DateRan].[Date] =startOfPeriod && 'ActiveEmp'[DateRan].[Date]=endOfPeriod)))

Any help is really appreciated! Thank you

[dax · Accepted solution]

Hi @Anonymous , 

"Step1 Get the active employee counts 12 month back", did you mean the duration is  2018/6/1-2019/6/1? If so , you could try to refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Mariusz]

Hi @Anonymous 

To get previous year count use SAMEPERIODLASTYEAR
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

For previous Month you can use PARALLELPERIOD

https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn