Past 12 months Average

[Ericwhv]

Hi All

I would like to work on some calutaion on past 12 month average of "Truck handle per HeadCount"

I have below existing formula

Trucks per HC = DIVIDE([Number of Trucks],[Total HC])

Total HC = CALCULATE(SUM('Cost&Budget'[Value]), 'Cost&Budget'[GNS Spent - USD]="HC")

Number of Trucks = DISTINCTCOUNT(Truck_Level[Truck_Level])

if i would like to know the pass 12 month average Trucks per HC, how can i do that?

for example, April, the trucker per HC is 32, i woud like to know the last 12 month from Mar to last year Apr average Truck per HC

Apr	32
Mar	21
Feb	22
Jan	22
Dec	18
Nov	19
Oct	12
Sep	15
Aug	12
Jul	10
Jun	9
May	8
Apr	4
Last12AVG	14.33

[Anonymous]

Hi @Ericwhv 

You can refer to the following sample

Sample data

Then create a measure

Measure = var a=SUMX(FILTER(ALL('Table'),OR('Table'[Year]=SELECTEDVALUE('Table'[Year])&&[Monthnum]<SELECTEDVALUE('Table'[Monthnum]),'Table'[Year]<SELECTEDVALUE('Table'[Year])&&[Monthnum]>=SELECTEDVALUE('Table'[Monthnum]))),[Trucks per HC])
return DIVIDE(a,12)

Output

Best Regards!

Yolo Zhu

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

[lbendlin]

please provide better sample data - at the minimum it needs to include the year.