Forum Discussion
Helper IFirst date fro groups of emails
Hi there,
I am trying to develop a dahsboard to report on our Marketing effectiviness. As part of this porject I want to show the best day to email our contacts to improve our email open rate. To do so I am trying to show when a contact is more inclined to open an email based on historic values.
Let's say I have Contact A and that I sent him 10 emails over a year. I would like to see when these emails were opened, maybe contact A opens his emails on a Thursday so it would make sense send him the emails on that day.
Now I have developed something but it is not working as I want. For email A I can see that Contact A actually opened it for the first time on a Thursday. The problem is that my dashboard is showing me that he opened the same email even on a Monday and on a Saturday and this would create problems with my next goal to measure the open rate.
Does anybody know how I can select only the first time the email was opened? I have found the following formula: DayOpens = CALCULATE(FIRSTDATE(Eloqua_EmailOpen[CreatedAt])) but it is still wrong cause it still shows me the opens for Monday and Saturday.
If I put it in plain english the formula that I would like to have is: 'Show me only the first date the email was opened relalated to the email that was sent'.
The data for Email_sent and Email_open are on 2 different data set but they are linked to eachother by Unique email address.
Please let me know if anybody has any idea how to tackle this.
Thanks in advance,
Alessandro
That is incredibly too little information to go on. "Doesn't work". It works for me, see attached PBIX.
I will need an explantion of "Doesn't work".
5 Replies
- Greg_Deckler
Community Champion
Well, one comment is that I think your use of CALCULATE is meaningless in that formula since you do not supply a filter clause. Second, try using MINX to get your date. With all that said, sample data and expected output are always extremely helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Alessandro-labaHi Greg,
These are the data:
Email sent:
Email Open:
This is the day filter I have in my dashboard:
So for Email A when I click on Tuesday I want to see the date value while when I click on Wednsday I don't wanna see any value for Email A since the first opened happened on the day before.
Thanks,
Ale
- Greg_Deckler
Right, so in your Email Open table, create a calculated column like this:
First Open = WEEKDAY( MINX( FILTER( ALL('Email Open'), [Email name] = EARLIER([Email name]) && [ContactID] = EARLIER([ContactID]) ), [Emai Open] ) )Then just tie your slicer to that column.