Date related total average on every row

[puck22]

Hello.

I'm trying to create a measure so I can show which opportunities are older than the average sales cycle on a table.

There is a special condition on my sales cycle measure, that I only calculate it based on the last 90 days, something like this:

Sales Cycle = 
CALCULATE(
AVERAGE(Opportunity[Age]),

FILTER(Opportunity,

Opportunity[CloseDate] < TODAY() && Opportunity[CloseDate] >= (TODAY() - 90)
))

What I'm having trouble to have the correct result is the average sales cycle for all opportunities in a Table visual, because it is giving me the result only for opportunities that have the close date on the last 90 days (because of the filter, if the filter is not there, the result is the same for every opportunity). Like this:

Opportunity	CloseDate	Age	Sales Cycle
A	04/27/2022	67	67
B	01/01/2021	64
C	02/15/2022	82	83

I want to have the same result for every opportunity on their row context, but also need this 90 days interval to be considered on the calculation. What I expect is something like this:

Opportunity	CloseDate	Age	Sales Cycle
A	04/27/2022	67	71
B	01/01/2021	64	71
C	02/15/2022	82	71

Any ideias how I can achieve this?

[v-chenwuz-msft]

Hi @puck22 ,

Please try this expression:

Sales =
VAR _s =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Opportunity'[Opportunity] ),
            Opportunity[CloseDate] < TODAY ()
                && Opportunity[CloseDate]
                    >= ( TODAY () - 90 )
        ),
        [Opportunity],
        "age1", SUM ( Opportunity[Age] )
    )
RETURN
    AVERAGEX ( _s, [age1] )

Best Regards

Community Support Team _ chenwu zhu

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