Forum Discussion
Average headcount (current + previous month)/2
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Count avg: = SUMX ( SUMMARIZE ( 'Calendar', 'Calendar'[Month-Year sort], 'Calendar'[Month-Year] ), CALCULATE ( VAR _currentmonth = SUM ( data[count] ) VAR _prevmonth = CALCULATE ( SUM ( data[count] ), OFFSET ( -1, ALL ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ), ORDERBY ( 'Calendar'[Month-Year sort], ASC ) ) ) RETURN IF ( _currentmonth && _prevmonth, DIVIDE ( _currentmonth + _prevmonth, 2 ) ) ) )
Hi Shrujan1612 - you can modify the way the previous month is retrieved and adjust the calculation for both the current and previous months
Average Current and Previous Month Headcount =
VAR SelectedDate = MAX('Presentation Calendar'[CALENDAR_DATE])
-- Get the previous month’s date
VAR PreviousMth = EDATE(SelectedDate, -1)
-- Get the last date of the previous month
VAR LastDateOfPreviousMonth = EOMONTH(PreviousMth, 0)
-- Calculate the current month’s headcount
VAR CurrentMonthHeadcount =
CALCULATE(
[TTM_headcount],
FILTER(
ALL('Presentation Calendar'),
'Presentation Calendar'[CALENDAR_DATE] = SelectedDate
)
)
-- Calculate the headcount for the last date of the previous month
VAR PreviousMonthClosingHeadcount =
CALCULATE(
[TTM_headcount],
FILTER(
ALL('Presentation Calendar'),
'Presentation Calendar'[CALENDAR_DATE] = LastDateOfPreviousMonth
)
)
-- Calculate the average of the current and previous month’s headcounts
VAR AverageHeadcount =
DIVIDE(CurrentMonthHeadcount + PreviousMonthClosingHeadcount, 2)
RETURN
IF(
ISBLANK(CurrentMonthHeadcount) || ISBLANK(PreviousMonthClosingHeadcount),
BLANK(), -- Handle cases where either value is blank
AverageHeadcount
)
Hope the above modified measure helps in your scenerio
Hi Rajendra,
Thankyou for your time.
Issue i am facing is with Previous month if you see my expected output I want previous month value in current for to sum current month + previous month but i am getting ouput as second screenshot. So, it is giving me wrong avg. And month year column i am considering from other calendar table as it required rolling 24 month .