Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Unique Customer 4 Week Average

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

DAX

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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