Perform an average on a distinctcount after having used summarize

[RogerSteinberg]

Hi,

I need to average out the past 2 months distinct count values.

Since my data is granular, I need to first calculate the distinctcount of users for the past 2 months and then perform the average on each month's value.

For example:

Columns: Month          DistinctCount        Previous2Months

Jan	100	-
feb	50	100
mar	69	72

VAR _distinctcount
SUMMARIZE(
    Query1,
    DateTable[YearMonthnumber],
    "distcount",DISTINCTCOUNT(Query1[User ID])
)
RETURN

Now how do I perform the average of the past 2 months only??????

[dax]

Hi @RogerSteinberg , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[RogerSteinberg · Accepted solution]

Thanks but wanted to avoid the allselected function. and seems like the date range for the computation is not what I wanted.

I managed to make it work with this formula:

CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            FactTable,
            DateTable[YearMonthnumber],
            "distinctC", DISTINCTCOUNT(FactTable[User ID])
        ),
        [distinctC]
    ),
    DATESINPERIOD (
        DateTable[Date],
        LASTDATE ( DATEADD(DateTable[Date],-1,MONTH )),
        -2,
        MONTH
    )
)

[amitchandak]

@RogerSteinberg , Try like

AverageX(
SUMMARIZE(
    Query1,
    DateTable[YearMonthnumber],
    "distcount",Rolling 12 = CALCULATE(DISTINCTCOUNT(Query1[User ID]),DATESINPERIOD(DateTable[Date],ENDOFMONTH(DateTable[Date]),-2,MONTH))  
),[distcount])
RETURN

[RogerSteinberg]

Im not sure why but it gives me the same exact numbers as my 

Calculate ( DISTINCTCOUNT(user_id)) column. It doesn't filter for only the past two months.