Creating a graph which shows how many days in a week that a customer visits

[g1davies]

HI,

I am trying to show how many days a customer visits per week.

I have all the visit data which has a line per visit (please note some customers visit more than once a day).

I would like to create a bar chart that shows how many customers have visited:

Once per week

Twice per week

Three times per week

Four times or more

and would also like this for each week of the year, to be able to show if we are increasing the amount of customer visits per week.

Can anyone help??

Thanks

Gavin

[TomMartens]

Hey,

here you find a pbix file with a very simpliefied model.

The model just has two tables: visits and calendar

 

I created two calculated columns in the visit table, this is necessary, because otherwise it's not possible to "sclice" the data in a visual

WeekNo just pulls the WeekNo from the Calendar table into the Visit table, this column is hidden from the report view.

And I also create a calculated column "frequency"

frequency = 
SWITCH(
CALCULATE(
    COUNT('Visit'[Customer]),
    ALLEXCEPT(Visit,Visit[WeekNo],Visit[Customer])
),
1,"1 visit",
2,"2 visits",
3,"3 visits",
"4 and more")

This calculation creates a frequency, by counting how often a customer has shown during a week.

The column chart shows the frequency during the weeks and linechart show that in the 2nd week there have been 3 distinct customer (just using the distinct count aggregate function on the customer in the visual)

 

Hope this gets you started

[g1davies]

Hi Tom,

That is really good thank you, I am nearly there. I have one issue which is i do not have an amount of visits coulum, I just have a line of data for each visit with customer details and date of visit, how do I create this coulmn?

Thanks

Gavin

[TomMartens · Accepted solution]

Hey,

then I would simply create an index column.

Open the query editor and edit your visit table: 

In addition I would first order the date column "Ascending"

If you find my posts helpful you may consider to mark one as a solution and may also want to kudo one of the posts

Hope this helps

And then add an index column

[g1davies]

Hi Tom,

Checking the data, I found another issue, some people are visiting more than once per day. for example someone who has visited 2 times for two days is counted as 4 or more visits but i need them to appear as 2 as they have come in for 2 days. can I do this?

Thanks

Gavin

[g1davies]

Awesome work, thanks