Forum Discussion

Shrujan1612's avatar
Shrujan1612
Helper I
1 year ago
Solved

Average headcount (current + previous month)/2

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 yearcurrent month valueprevious month valueAvg
Oct-231099.5
Nov-23111010.5
Dec-23121111.5
Jan-24131212.5
Feb-24141313.5
Mar-24151414.5
Apr-24161515.5
May-24171616.5
Jun-24181717.5
Jul-24191818.5
Aug-24201919.5
Sep-24212020.5

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

month yearcurrent month valueprevious month value 
Oct-23101010
Nov-23111111
Dec-23121212
Jan-24131313
Feb-24141414
Mar-24151515
Apr-24161616
May-24171717
Jun-24181818
Jul-24191919
Aug-24202020
Sep-2421 10.5
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

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

     

     

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      Shrujan1612
      Helper I
      1 year ago

      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 .