Average headcount (current + previous month)/2

[Shrujan1612]

I want to calculate (current + previous month)/2
Below is the measure i am using for average calculation.

Average Current and Previous Month Headcount =
VAR SelectedDate = MAX('Presentation Calendar'[CALENDAR_DATE])

VAR PreviousMth = EDATE(SelectedDate, -1)

VAR LastDateOfPreviousMonth = EOMONTH(PreviousMth, 0)

VAR CurrentMonthHeadcount =

    CALCULATE(

        [TTM_headcount],

        FILTER(

            ALL('Presentation Calendar'),

            'Presentation Calendar'[CALENDAR_DATE] = SelectedDate

        )

    )

VAR PreviousMonthClosingHeadcount =

    CALCULATE(

        [TTM_headcount],

        FILTER(

            ALL('Presentation Calendar'),

            'Presentation Calendar'[CALENDAR_DATE] = LastDateOfPreviousMonth

        )

    )

VAR AverageHeadcount = DIVIDE(CurrentMonthHeadcount + PreviousMonthClosingHeadcount, 2)

RETURN  AverageHeadcount

Sample:
Expected output

month year	current month value	previous month value	Avg
Oct-23	10	9	9.5
Nov-23	11	10	10.5
Dec-23	12	11	11.5
Jan-24	13	12	12.5
Feb-24	14	13	13.5
Mar-24	15	14	14.5
Apr-24	16	15	15.5
May-24	17	16	16.5
Jun-24	18	17	17.5
Jul-24	19	18	18.5
Aug-24	20	19	19.5
Sep-24	21	20	20.5

---------------------------
Current output:

month year	current month value	previous month value
Oct-23	10	10	10
Nov-23	11	11	11
Dec-23	12	12	12
Jan-24	13	13	13
Feb-24	14	14	14
Mar-24	15	15	15
Apr-24	16	16	16
May-24	17	17	17
Jun-24	18	18	18
Jul-24	19	19	19
Aug-24	20	20	20
Sep-24	21		10.5

[Jihwan_Kim · Accepted solution]

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 ) )
    )
)

[rajendraongole1]

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

[Shrujan1612]

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 .