null

[datagorillagirl]

null

[v-yangliu-msft · Accepted solution]

Hi  @datagorillagirl ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Fiscal Month])

2. Create measure.

Flag =
VAR _count =
    COUNTX ( ALLSELECTED ( 'Table 2' ), 'Table 2'[Fiscal Month] )
VAR _select =
    SELECTCOLUMNS ( 'Table 2', "1", [Fiscal Month] )
VAR _min =
    MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month] IN _select ), [Date] )
VAR _minID =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] ) = YEAR ( _min )
                && MONTH ( 'Table'[Date] )
                    = MONTH ( _min ) - 1
        ),
        [Fiscal Month ID]
    )
VAR _max =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month] IN _select ), [Date] )
VAR _maxdate =
    DATE ( YEAR ( _max ), MONTH ( _max ), 1 )
VAR _secondID =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] < _maxdate ), [Fiscal Month ID] )
VAR _allsum1 =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month] IN _select ),
        [Ending HC]
    )
        + SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month ID] = _minID ),
            [Ending HC]
        )
VAR _if1 =
    DIVIDE ( _allsum1, _count + 1 )
VAR _allsum2 =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month] IN _select ),
        [Ending HC]
    )
        + SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month ID] = _minID ),
            [Ending HC]
        )
        + SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Month ID] = _secondID ),
            [Ending HC]
        )
VAR _if2 =
    DIVIDE ( _allsum2, _count * 2 )
RETURN
    IF ( _count = 1, _if1, _if2 )

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[datagorillagirl]

Do you have an alternative without creating a table ?

[jgeddes]

Try

Average Headcount =

var _lowHC =

min(fiscalMonthHC[Fiscal Month ID])-1

var _highHC =

max(fiscalMonthHC[Fiscal Month ID])

Return

averagex(

    filter(all(fiscalMonthHC), fiscalMonthHC[Fiscal Month ID] >= _lowHC && fiscalMonthHC[Fiscal Month ID] <= _highHC),

    [Ending HC]

)