Forum Discussion

Benegade's avatar
Benegade
Regular Visitor
8 years ago
Solved

Linking tables on date

Hi, I am trying to create a report that shows the effect of advertising campaigns on products. i.e. how many units (or in my case, passengers booked) do we sell as the result of an advertising cam...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi Benegade,

     

    Assume the sample data below: 

     

     

    You can create a measure like this: 

     

    Measure = CALCULATE(SUM('Bookings'[Pax]),FILTER('Bookings',IF(MAX('MarketingActivity'[Appearance Date])<='Bookings'[BookedDate],DATEDIFF(MAX('MarketingActivity'[Appearance Date]),'Bookings'[BookedDate],DAY),BLANK())<3 &&'Bookings'[Destination]=MAX('MarketingActivity'[Destination])))

     

    For more information, you can download attached pbix file. If it doesn't meet your requirement, please share some sample data with us and clarify corresponding results. 

     

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

Hi Benegade,

 

Assume the sample data below: 

 

 

You can create a measure like this: 

 

Measure = CALCULATE(SUM('Bookings'[Pax]),FILTER('Bookings',IF(MAX('MarketingActivity'[Appearance Date])<='Bookings'[BookedDate],DATEDIFF(MAX('MarketingActivity'[Appearance Date]),'Bookings'[BookedDate],DAY),BLANK())<3 &&'Bookings'[Destination]=MAX('MarketingActivity'[Destination])))

 

For more information, you can download attached pbix file. If it doesn't meet your requirement, please share some sample data with us and clarify corresponding results. 

 

 

Best Regards,
Qiuyun Yu 

Benegade's avatar
Benegade
Regular Visitor
8 years ago

Hi v-qiuyu-msft,

 

Thanks for the reply - that helped a lot.

 

I modified the formula slightly so that it excluded bookings made prior to the marketing date appearance, and found that I needed to replace the blank() with an emptry string because it was viewing blank as 0, but otherwise it worked perfectly.

 

The final formula which seems to be working for me is:

 

Measure = CALCULATE(SUM('Bookings'[Pax]),FILTER('Bookings',
IF(MAX('MarketingActivity'[Appearance Date])<='Bookings'[BookedDate],DATEDIFF(MAX('MarketingActivity'[Appearance Date]), 'Bookings'[BookedDate],DAY),"") <=3
&& IF(MAX('MarketingActivity'[Appearance Date])<='Bookings'[BookedDate],DATEDIFF(MAX('MarketingActivity'[Appearance Date]), 'Bookings'[BookedDate],DAY),"") >=0
&&'Bookings'[Destination]=MAX('MarketingActivity'[Destination])))

 

Kind regards,

Ben