cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djbuncle Frequent Visitor
Frequent Visitor

Issue with DATESBETWEEN respective to multiple dates

Hi.

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.

 

 

CampaignCubeModel.pngCampaign Model

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Thanks.

1 REPLY 1
Highlighted
Super User
Super User

Re: Issue with DATESBETWEEN respective to multiple dates

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!