Creating bar chart total visits with dynamic distinct count

[Andreas_Wacker]

Hello all,

a task that seems simple at first sight gets more complicated with every hour I spend trying, reading and thinking about it. If there is already a post about this, I didn't notice it because I guess I don't have the right words to describe it.

I have a chart with Customer_ID and Date(YYYY.MM.DD):

Customer_ID	Date (YYYY.MM.DD)
1	2021.01.01
2	2021.01.02
3	2021.01.03
1	2021.01.04
1	2021.01.05
2	2021.01.06
4	2021.01.07
1	2021.01.08

At the end I want to create a chart from these data by
Axis (X) = Number of visits in period X (1 visit/ 2 v. / 3 v. / etc.)
Values = Number of unique customers in period X
Period X = dynmaically selected

Two bar chart examples:
Period 2021.01.01 to 01.08

Period 2021.01.03 to 01.08

Best

Andreas

[Andreas_Wacker]

Hello Jihwan_Kim,

it works. Thank you for your help.

Best,

Andreas

[Andreas_Wacker]

Thank you JiHwan Kim for your quick reply. 

To understand it correctly, I have to create a new table with the unique ID's before I can start with the visualization. There is no other solution?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I created the dimension table for CustomerID because

- I do not know how your actual data model looks like. 

- in order to make data model a bit more easy to understand.

No need to create this dimension table.

[Jihwan_Kim · Accepted solution]

Customers Count : =
VAR _currentvisitcount =
MAX ( 'Visit Count'[Visit Count] )
VAR _customersvisitcount =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Customers[Customer_ID] ),
"@visitcount", CALCULATE ( COUNTROWS ( Data ) )
),
[@visitcount] = _currentvisitcount
)
RETURN
COUNTROWS ( _customersvisitcount )

Link to the sample pbix file