Forum Discussion
Solved
Average Headcount
Hi Everyone and Ashish_Mathur amitchandak I Have months from April to March as I follow India financial year and opening and closing headcount as follows along with starts, exits, monthly attr...
I was able to crack it. Thank for everyone who looked at it.
new average =VAR CurrentMonth = MAX('Date'[Date])VAR OpeningHC = [open hc]VAR ClosingHC = [close hc]VAR CumulativeClosingHC =CALCULATE(SUMX(VALUES('Date'[Date]),[close hc]),FILTER(ALL('Date'),'Date'[Date] <= CurrentMonth))VAR MonthNumber = MONTH(CurrentMonth)RETURNSWITCH(TRUE(),MonthNumber = 4, (OpeningHC + ClosingHC) / 2,MonthNumber = 5, (CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 2,MonthNumber = 6, (CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 3,MonthNumber = 7, (CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 4,MonthNumber = 8, (CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 5,MonthNumber = 9, (CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 6,MonthNumber = 10, (CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 7,MonthNumber = 11, (CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 8,MonthNumber = 12, (CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 9,MonthNumber = 1, (CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 10,MonthNumber = 2, (CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 11,MonthNumber = 3, (CALCULATE([close hc], DATEADD('Date'[Date], -11, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 12,(CumulativeClosingHC + OpeningHC) / MonthNumber)
I was able to crack it. Thank for everyone who looked at it.
new average =
VAR CurrentMonth = MAX('Date'[Date])
VAR OpeningHC = [open hc]
VAR ClosingHC = [close hc]
VAR CumulativeClosingHC =
CALCULATE(
SUMX(
VALUES('Date'[Date]),
[close hc]
),
FILTER(
ALL('Date'),
'Date'[Date] <= CurrentMonth
)
)
VAR MonthNumber = MONTH(CurrentMonth)
RETURN
SWITCH(
TRUE(),
MonthNumber = 4, (OpeningHC + ClosingHC) / 2,
MonthNumber = 5, (CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 2,
MonthNumber = 6, (CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 3,
MonthNumber = 7, (CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 4,
MonthNumber = 8, (CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 5,
MonthNumber = 9, (CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 6,
MonthNumber = 10, (CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 7,
MonthNumber = 11, (CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 8,
MonthNumber = 12, (CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 9,
MonthNumber = 1, (CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 10,
MonthNumber = 2, (CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 11,
MonthNumber = 3, (CALCULATE([close hc], DATEADD('Date'[Date], -11, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -10, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -9, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -8, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -7, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -6, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -5, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -4, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -3, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -2, MONTH)) + CALCULATE([close hc], DATEADD('Date'[Date], -1, MONTH)) + ClosingHC) / 12,
(CumulativeClosingHC + OpeningHC) / MonthNumber
)