Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculating Average Value using Beginning and Ending Value

I have the following three piece measures

CurrentHC = CALCULATE(SUM(HeadCountActuals[ActualPeopleCount]))
PreviousHC = CALCULATE (
    SUM( HeadCountActuals[ActualPeopleCount] ),
    'HeadCountActuals'[ActualPeopleCount] <> BLANK (),
    PARALLELPERIOD ( '2. Time'[Date], -1, MONTH )
)
monthcount = DISTINCTCOUNTNOBLANK(HeadCountActuals[FiscalMonthId])
AverageHC = DIVIDE([CHC]+[PHC],[monthcount]*2)

My first question is help simplifying the model to include CurrentHC, PreviousHC and monthcount as variables

My second question is when I select consecutive months such as in

However when there is a gap in dates, the AverageHC returns incorrect value

For example in above the AverageHC displays 17.5 when it should be 14.25

The basis of the calculation is below

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

     

    Average HC = 
var a = ADDCOLUMNS(VALUES(Headcount[Month]),"CHC",var m=[Month] return CALCULATE(sum(Headcount[HC]),Headcount[Month]=m))
var b = ADDCOLUMNS(a,"PHC",var m=[Month] return if([Month]=0,[CHC],CALCULATE(sum(Headcount[HC]),Headcount[Month]=m-1)))
return averagex(b,DIVIDE([CHC]+[PHC],2,0))

     

     

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

     

    Average HC = 
var a = ADDCOLUMNS(VALUES(Headcount[Month]),"CHC",var m=[Month] return CALCULATE(sum(Headcount[HC]),Headcount[Month]=m))
var b = ADDCOLUMNS(a,"PHC",var m=[Month] return if([Month]=0,[CHC],CALCULATE(sum(Headcount[HC]),Headcount[Month]=m-1)))
return averagex(b,DIVIDE([CHC]+[PHC],2,0))