Forum Discussion

Ericwhv's avatar
Ericwhv
Icon for Helper II rankHelper II
3 years ago

Past 12 months Average

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

Apr32
Mar21
Feb22
Jan22
Dec18
Nov19
Oct12
Sep15
Aug12
Jul10
Jun9
May8
Apr4
Last12AVG14.33
Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.