topic Re: Creating a calculated column from another table based on date criteria in DAX Commands and Tips

Creating a calculated column from another table based on date criteria

Anonymous — Sat, 04 May 2019 12:37:05 GMT

Example file with Sales and Mailers Tables

Example End Product Conversion Chart by Campaign

Background:

We help boat dealers sell boats, we do marketing for them.

I have 2 tables:

Mailer table – telling us who we physically mailed a marketing campaign to (as well as details on the mailer)
Sales Table – telling us who actually bought our widget

Problem:

I'm looking to visualize conversions by different physical mail "Campaign" we send out. Right now there’s no column in our Sales table that says which campaign resulted in a sale.

I need to add a “Last attributed Campaign” column in my Sales Table so I can look at (Count of campaign type mailed [Mailers Table]) /(Count of campaign type resulting in a sale [Sales Table]) – to get conversion by campaign. All I need is the column in the sales table to do this.

Complex If / Vlookup type request

How can we add a “Last Attributed Campaign” column into the Sales table with the below conditions?

Look at the “Deal Date” in the Sales Table, and if it is on the 13th of the month or before, then the column should return the Campaign from the previous month or earlier, whatever is latest.

For example, if someone bought a boat on May 5th, we want to attribute that Boat sale to the "Campaign" that was sent before May.

If the deal date is on the 14th of the month or after, simply return the most recent "Campaign" sent – could be the same month
Every row in the Sales table should be filled in based on what’s in the Mailer table.

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 17:17:31 GMT

Anonymous quick question, I think you want to get the campaign id based on store / customer, not only just dealdate

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 17:20:10 GMT

Hi parry2k - Yes, you are right, sorry for the confusion!

Basically the last Campaign the customer received before they actually bought a boat, based on those date criterias.

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 17:21:14 GMT

Anonymous how about store? Does that matter? I noticed campaigns are by store

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 17:24:31 GMT

parry2k Store is important because we send campaigns on behalf of a store, but it's really which campaign triggered the customer to buy.

Then I can show which campaigns are performing well for individual stores.

Thank you so much for your help on this!

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 17:26:03 GMT

We can send the same type of campaign to multiple stores

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 17:26:27 GMT

Anonymous before i go deep into it, can you check if following campaign id i calculated for each dealdate looks correct based on the logic you provided.

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 17:27:10 GMT

I reviewed this post when I was researching my problem but I think it is off point. I have a string of tables that are related to one another Booking => Order => Division. I am trying to create a calculated column in my Booking table based on something in my Division table. I beleive I need to create two related statements with the lookupvalue statement but am not sure how to do this.

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 17:40:34 GMT

Anonymous this is how sales looks like against each campaign based on the data, does it make sense?

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 18:16:36 GMT

Hi parry2k - sorry for late response - everything in your original table looks correct, except for 2 callouts.

Customer ID BM0001118195, the answer should be M1, not M5
Customer ID BM000190996 – should be M1 not M5

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 18:24:40 GMT

AnonymousI have solution ready, let me know where you want to send me. atleast this will get you started. Although on larger schema I will improve/make changes to data model.

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 18:25:07 GMT

Hi parry2k !

Yes that looks right if all the numbers for Campaign sales are correct, I think I only saw 2 examples that didn't flow through, but the below is absolutely correct for what I'm looking for !!!

If I'm able to add the "Campaign Attributed" Column in the Sales table - I should be able to break out the performance of campaigns for different dealers as well!

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 18:32:17 GMT

Thank you so much parry2k - if you're not able to add it here (feel free if you prefer to add for anyone else to see), I have google drive or one-drive, not sure what works best for you to share?

Re: Creating a calculated column from another table based on date criteria

Anonymous — Mon, 06 May 2019 20:20:02 GMT

Hi parry2k !

Thank you again, do you mind uploading to this one-drive folder please?

https://1drv.ms/f/s!AuheBS0uRNVlmDiCBAHEaJ7Yt-LV

Thanks again!

Re: Creating a calculated column from another table based on date criteria

parry2k — Mon, 06 May 2019 20:22:17 GMT

Anonymous attached with this reply.

Re: Creating a calculated column from another table based on date criteria

Anonymous — Thu, 09 May 2019 01:13:16 GMT

parry2k Thank you so much!

Re: Creating a calculated column from another table based on date criteria

Anonymous — Tue, 21 May 2019 23:39:28 GMT

parry2k - sorry for late response, just a follow up to better understand campaign sales in the mailers table.

How exactly does this formula below work?

It gives me the right answer, but having trouble following the logic.

Campaing Sales = CALCULATE( COUNTROWS( Sales ), TREATAS( VALUES( Mailers[Campaign] ), Sales[Campaign] ) )

Re: Creating a calculated column from another table based on date criteria

Anonymous — Thu, 23 May 2019 14:37:16 GMT

parry2k

I did a bit more research, and It would be a ton of help if I could understand the logic below, but also I'm wondering why we didn't use a summarize along with customer id so we join on both customer id and mailers?

It seems to be doing everything correctly, but I saw others using a summarize function.

Campaing Sales = CALCULATE( COUNTROWS( Sales ), TREATAS( VALUES( Mailers[Campaign] ), Sales[Campaign] ) )