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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors