Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 12 Month Counts

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 IDLocation
5/1/2020101ABC
5/1/2020102DEF
4/1/2020101ABC
4/1/2020102DEF
6/1/2019101AAA
6/1//2019103WWW
6/1/2019105EEE

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
Need Help
Show and Tell
  • dax's avatar
    dax
    6 years ago

    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.

     

2 Replies