cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RykerZyker
Helper I
Helper I

Unique total for specific days & 7,14,21 days later...

PBI File

Hi, I need to find the unique total for all of my events [Total People on Event date] and then if those specific people appeared 7 days later from the day of the event do a total of that and If an event has multiple dates, do a total for all those dates and look at the last date for the 7 days later analysis.

 

The goal of this task is to see how many people that initially attended the event were present weeks later and also compare them to the total for that specific day in a bar chart, line chart (or whatever).

 

Relevant Tables: 

Fact Table[ContactID,Date] * to 1 Dimension Contact: [ContactID]

Event Table with no relations. [Event Name, Event Date] 

Date Table

 

Expected Result:

The blue bar on the left is the total for that event/campaign and orange is the total for that day. Then week 1 will be the 7 days later total and then the blue bar is how many people that attended campaign 1 our present then the orange is the total for that specific day.

Capture.PNG

From my limited understanding of PBI how I want this to work is by having a slicer with event names and when a specific event is chosen it shows the data in a chart similar to the one above. Any recommendations are welcome

 

TY 🙂
Any help on this is greatly appreciated. I've somewhat achieved this result by making custom columns for each day and then a measure for totals, but this is definitely not the way to do it as I would end up with 100s of columns and it doesn't even do totals properly.

6 REPLIES 6
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Hi, I've already posted the sample data. It's the "PBI File" hyperlinked at the very top of the page. I've done my best to describe the expected result in the post under 'expected result" if you need further explaniation, let me know and I'll do my best.

Apologies for missing the link - will have a look at the file.

 

Edit = You have multiple events overlapping. How do you decide which event the contact attended on that day?

@lbendlin Completely missed this edit. Those overlaps occurred when I was changing the dates for the sample data but in general, include all dates even if they overlap. So If a contact attended two events on the same day include both events.

@lbendlin Sorry to keep bothering, but have you managed to find anything that might help?

No worries. Actaully I've managed to solve half of the problem and the measure used is in this PBI file: https://drive.google.com/file/d/1tMYZYQGsoyNE-4OOekc8Sxv5XNRZ-Sh7/view?usp=sharing

I now just need to see the total unique for the 7D (7 days later) column.

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!