How to calculate average weekly active visitor using DAX in PowerBI

Question

*Take note that Visit 1 means the first visit of the day while Visit 2 means the 2nd visit for the day.

Hello everyone,

I'm trying to calculate average weekly active visitors in PowerBI using DAX.

First, I need to calculate how many unique visitors visit the shop in one week (Mon-Sun). Based on the calendar, 5/10/2022 - 5/13/2022 will be categorized under one week, and 5/16/2022 - 5/18/2022 is another week. So for the week from 5/10/2022 - 5/13/2022, there are 9 unique visitor and for the week from 5/16/2022 - 5/18/2022, there are 3 unique visitors.

Once I found out the unique visitor for respective weeks, then I can get the average weekly active visitor by:

(9+3)/2 = 6 visitors

Hence, the answer for the example in the screenshot will be 6 visitors. The output will be used in card visualization.

Struggling to find the best way to do this, any help or advise will be greatly appreciated!

Sample Data:

https://docs.google.com/spreadsheets/d/10TsJUy-Lkdpb9Eeh5itx-XE59hytC_i5I_6UeLLHS84/edit#gid=0

user_19042379 · Answer

VAR days = 
        SUMMARIZE(
            'Sheet1'
            ,'Sheet1'[Date]
        )
VAR Calend=
        ADDCOLUMNS(
            days
            ,"week",WEEKNUM('Sheet1'[Date])
        )
VAR weeks= 
        SUMMARIZE(
            Calend
            ,[week]
            ,"qty",COUNTROWS(VALUES('Sheet1'[Visitor ID]))
        )         
RETURN
    AVERAGEX(
        weeks
        ,[qty]
    )

user_2872922 · Answer

I don't know enough about DAX (or Power BI) to devise a DAX only solution.

However, you can calculate the Distinct Visitors per week using Power Query M Code (Home=>Transform in Power BI), and then create a Measure to show the Average visitors per week on the card.

Power Query M Code
Edit: Change from computing weeknumber to computing start of week to avoid problems if dates span more than a year, per comment below by @weizer

let

//change next line to whatever your actual source is
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Don't need this column for the output
    #"Removed Columns" = Table.RemoveColumns(Source,{"Visit"}),

//Set data types
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Visitor ID", Int64.Type}, {"Date", type date}}),

//add custom column referenced to "StartOfWeek" so we can group by week
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekNumber", each Date.StartOfWeek([Date],Day.Monday), type date),

//Group by week number
//  Then aggregate by week range and distinct visitor count
    #"Grouped Rows" = Table.Group(#"Added Custom", {"WeekNumber"}, {
        {"Date Range", each Date.ToText(List.Min([Date])) & " - " & Date.ToText(List.Max([Date])), type text},
        {"Distinct Visitors", each List.Count(List.Distinct([Visitor ID])), Int64.Type}
        }),

//Remove unneeded Weeknumber column
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"WeekNumber"})
in
    #"Removed Columns1"

The Measure for the card will be just a simple Average function:

Distinct Visitors per Week = Average(yourTableName[Distinct Visitors])

How to calculate average weekly active visitor using DAX in PowerBI

2 Answers