Unique Customer 4 Week Average

[pbi_baller]

DAX help needed.. I am trying to calculate a a 4 week average for unique visitors.. using the 4 weeks prior to the maximum selected YearWeek.

In Above selection, it should count uniques for week 36 to week 39 and then divide by 4. However, need a unique count for each week then divide by 4. 

DAX I tried:

Week Rank = RANKX(ALL(DimDate), DimDate[Rep_Week],, ASC, Dense)//YYYYWW

CALCULATE(
AVERAGEX(VALUES(DimDate[Rep_Week]),
CALCULATE(DISTINCTCOUNT(Fact_KPI_Visits[MembershipNumber]),
FILTER(ALL(DimDate), DimDate[Week Rank]>= MAX(DimDate[Week Rank])-5 && DimDate[Week Rank]<= MAX(DimDate[Week Rank])-1 )
)
)
)

Any help greatly appreciated

[v-binbinyu-msft]

Hi @pbi_baller ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu

[pbi_baller]

bump