I'm having trouble with a DAX calculation which is ultimately trying to show how many people opted-out of marketing within a 3 day timeframe of having received a campaign promotion.
So in the model here we I want the sum of Activity[Opt Out Count] where Activity[Activity Date] is within 2 days of receiving the promotion, so within 2 days of 'Campaign Actions'[Campaign Date].
I tried using;
Campaign Opt Out Count := CALCULATE(SUM([Opt Out Count]), FILTER('Activity Date', DATESBETWEEN('Activity Date'[Activity Date], 'Campaign Actions'[Campaign Date], DATEADD('Campaign Actions'[Campaign Date],2,DAY))))
However I get the error
A single value for column [Campaign Date] in table [Campaign Actions] cannot be obtained.
There are obviously multiple campaigns in the [Campaign Actions] table repeating the campaign date for each row (PlayerId and CampaignSkey). The idea is to pivot the data, one line per campaign showing the aggreagated number of recipients vs the number of those players who opten-out of marketing within 3 days.
* Obviously this can possibly cause the opt-out to be attributed to multiple campaigns but this is not an issue since the camapigns are spead-out.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
Proud to be a Datanaut!