Forum Discussion
Displaying customer population over time
Microsoft EmployeeHi lee_,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem in below picture and I have created a new table to record the time period.
2.
Create a new measure to calculate your customer amount in different period.
Amount of customer = CALCULATE(COUNT(Sheet1[CustomerID]),FILTER('Sheet1','Sheet1'[In]>=MAX('Sheet2'[Time]) && 'Sheet1'[Out]<=MAX('Sheet2'[Time+15])))
3.Create a Line chart visual and add the [Amount of customer] and [Time] field. Now you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/pmxbwhjihin9m40/Displaying%20customer%20population%20over%20time.pbix?dl=0
Regards,
Daniel He
Thanks for the response Daniel,
Unfortunately I don't think this is going to work for my instance where there are 100K+ visits over the course of the year. The chart takes an exceedingly long time to generate.
I tried a different way, creating a table, Times, with a column of 15-minute intervals using:
Time = GENERATESERIES(Date(2017,7,11), DATE(2018,7,1), (1/1440)*15)
and then on that table adding a column
In Store = SUMX(Customers, IF(AND(Customers[In]<=Times[Time], Customers[Out]>Times[Time]),1,0))
Although the extra column took a long time to calculate, the chart was a bit more fluid. Unfortunately, this solution means that I am unable to drill down/slice against other Customer parameters (gender, age, etc).
I suspect I am going to have to re-evaluate my underlying model. But is there a more efficient/dynamic way of doing this in Power BI?
Thanks again.