Count Overlap by Date

Hello!

I have a list of Customer IDs, Dates, City, and State. I am trying to figure out how to show all the customers that have attended the first show date, the second show date, and then both dates in a bar graph or something equally digestible. I’d like to show both distinct and total if possible.

An exerpt of my data:

Things I have tried:

Creating a matrix with count of Date which works until I try to get it into a visualization.

Creating a column of show count:

Show Count = CALCULATE(COUNTA(Sheet1[Cust ID]),FILTER(Sheet1,Sheet1[Cust ID]=EARLIER(Sheet1[Cust ID],1)))

That didn’t quite give me what I need because I really want to show 1s and 0s so I can aggregate the counts into a graph.

Creating columns for show 1, show 2, and both:

Show 1 = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[Date].[Year]=2019))

But that gave me a total count not a row by row.

What I have in my head:

Thank you!

Need Help

ryan_mayu
4 years ago
jenwho523

here is a workaround for you.pls see the attachment below
show.pbix23 KB

5 Replies

ryan_mayu
Super User
4 years ago
jenwho523

could you pls paste the sample data, not the screenshot? What's more, could you pls provide also provide the expected output which based on the sample data you provided? (the screenshot of chart in excel is also fine)

jenwho523

New Member

4 years ago

ryan_mayu

Yep! Here is the data pasted. And below is my expected output with dummy data. I don't have it based on the current data I have because I can't get there yet.

Cust ID	Date	City	State
100000000000192	3/30/2019	New Orleans	LA
100000000000192	3/30/2019	New Orleans	LA
100000000000912	3/30/2019	New Orleans	LA
100000000002976	3/30/2019	New Orleans	LA
100000000002976	3/30/2019	New Orleans	LA
100000000002976	3/30/2019	New Orleans	LA
100000000042960	3/30/2019	New Orleans	LA
100000000050352	3/30/2019	New Orleans	LA
100000000050352	3/30/2019	New Orleans	LA
100000000050352	3/30/2019	New Orleans	LA
100000000061920	3/30/2019	New Orleans	LA
100000000093120	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000096768	3/30/2019	New Orleans	LA
100000000229680	3/30/2019	New Orleans	LA
100000000229680	3/30/2019	New Orleans	LA
100000000229680	3/30/2019	New Orleans	LA
100000000297600	3/30/2019	New Orleans	LA
100000000297600	3/30/2019	New Orleans	LA
100000000386544	3/30/2019	New Orleans	LA
100000000397440	3/30/2019	New Orleans	LA
100000000397440	3/30/2019	New Orleans	LA
100000000448464	3/30/2019	New Orleans	LA
100000000476544	3/30/2019	New Orleans	LA
100000000494928	3/30/2019	New Orleans	LA
100000000589488	7/31/2021	New Orleans	LA
100000000665280	3/30/2019	New Orleans	LA
100000000665280	3/30/2019	New Orleans	LA
100000000684864	7/31/2021	New Orleans	LA
100000000703728	3/30/2019	New Orleans	LA
100000000703728	3/30/2019	New Orleans	LA
100000000994512	3/30/2019	New Orleans	LA
100000001023264	3/30/2019	New Orleans	LA
100000001023264	3/30/2019	New Orleans	LA
100000001023264	3/30/2019	New Orleans	LA
100000001023264	3/30/2019	New Orleans	LA
100000001056240	7/31/2021	New Orleans	LA
100000001289280	7/31/2021	New Orleans	LA
100000001418016	7/31/2021	New Orleans	LA
100000001434528	3/30/2019	New Orleans	LA
100000001434528	3/30/2019	New Orleans	LA
100000001434528	3/30/2019	New Orleans	LA
100000001674432	3/30/2019	New Orleans	LA
100000001674432	3/30/2019	New Orleans	LA
100000001674432	3/30/2019	New Orleans	LA
100000001997904	3/30/2019	New Orleans	LA
100000001997904	3/30/2019	New Orleans	LA
100000001997904	7/31/2021	New Orleans	LA
100000001998816	3/30/2019	New Orleans	LA
100000002059824	7/31/2021	New Orleans	LA
100000002059824	7/31/2021	New Orleans	LA
100000002101008	3/30/2019	New Orleans	LA
100000002132208	3/30/2019	New Orleans	LA
100000002155200	3/30/2019	New Orleans	LA

ryan_mayu
Super User
4 years ago
jenwho523

here is a workaround for you.pls see the attachment below
show.pbix23 KB
- jenwho523
  New Member
  4 years ago
  ryan_mayu That will work! Thank you!

Forum Discussion

Count Overlap by Date

5 Replies

Related content

Overlap Count Measure

Find Overlapping Dates

2 visuals overlapping

Date Overlap Check and Evaluate Validity of Overlap

Counting nulls