AVG of a measure based on dates pulled in

[Trying2Excel]

I would like to achieve a measure to calculate an average of employee count based on the dates that are pulled in. For example, using the screen shot below, for Dec 22 the calculation would be =AVG(SUM(2285+2328),2).....2 in the equation is the count if current period and previous period.

the Jan 2023 denominator would be 3 and so forth.

I need the formula to be adaptable for any range of periods.

[MohammadLoran25 · Accepted solution]

Hi @Trying2Excel 

1-In your DateTable, Create a calculated column as below:

YearMonthIndex=YEAR(DateTable[Date])*12+MONTH(DateTable[Date])

It gives you the index for combinations of year and month.

2-Then Create your measure as this:

VAR _MINIMUMMONTHYEARINDEX=CALCULATE(MIN(DateTable[YearMonthIndex]),ALLSELECTED ( DateTable ))

 RETURN

CALCULATE (

    AVERAGEX(VALUES(DateTable[YearMonthIndex]),[salesss]),

    FILTER (

        ALL ( DateTable ),

        DateTable[YearMonthIndex]

                <= MAX ( DateTable[YearMonthIndex] )

                &&

                DateTable[YearMonthIndex]>=_MINIMUMMONTHYEARINDEX

    )

)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran

[Trying2Excel]

Hi! Thank you for the quick response, the YEAR & MONTH functions are not allowing me to select a table[column], only measures. Am I doing something wrong?

[MohammadLoran25]

@Trying2Excel 

I think your are putting it in a measure so it is the reason.

Create it as a calculated column. Then it would be OKay.

[Trying2Excel]

Okay figure that out. These are my results. The second image are the numbers I am trying to get to...

[MohammadLoran25]

Did you put latest version of my measure?

It includes variables.

[Trying2Excel]

Yes! I have updated the measure and I am getting all sorts of errors...

VAR _MINIMUMMONTHYEARINDEX = CALCULATE(MIN(Periods[MonthYearIndex]),ALLSELECTED ( Periods ))

 RETURN

CALCULATE (

    AVERAGEX(VALUES(Periods[MonthYearIndex]), 'Worker Type'[Worker Count]),

    FILTER (

        ALL ( Periods ),

        Periods[MonthYearIndex]

                <= MAX (Periods[MonthYearIndex]) && Periods[MonthYearIndex] >=_MINIMUMMONTHYEARINDEX

    ))

[MohammadLoran25]

It is because you do not have set any name for your measure.

MeasureName=

My Formula 

[Trying2Excel]

Yikes! Rookie here. 🙂 It worked! That is awesome! One last thing do you know why the Index would be duplicating like this:

[MohammadLoran25]

Should not be like that.

Send your formula. Is it set on "Don't be summarized" option?

[Trying2Excel]

Discard figured it out! Thank you so much for your help!