Is MIN/Filter the right approach? Help - Sample data in link

[vehloaaron]

Hi Power BI Community - 

Our marketing leaders have asked if we can build out some custom attribution models for marketing campaigns in PowerBI.  Based on what I know of the data, this can be done.  I think I have a good idea of how to approach this but I am hitting a snag using MIN/FILTER DAX to get what I need.  I have some sample data and the desired results and I am hoping someone in the community can assist or point me in the right direction.  

Business Problem:  Leads respond to marketing campaigns at various points in their buying cycle.  We would like to attribute a percentage of the won booking dollar value to the marketing campaign a lead responded before various milestones in the buyers journey.  Each of these milestones have a date/time recorded in our database

The milestones are:  

1. Lead Created in System 
2. Opportunity Created in our System
3. Opportunity Closed Won in our System

We would like to look at our opportunity table and a campaign response table and pull the date that is equal to or less than the date of the milestone and attribute a percent of the won deal to that touch point.  

Our model to attribute a percent of won opportunity dollar value to the campaign:

1. Lead Created -- Attribute 50%
2. Opportunity Created -- Attribute 25%
3. Opportunity WOn -- Attribute 25%

HERE IS A PBIX FILE with sample data

Here is what would be a desired outcome: 

[Ashish_Mathur]

Hi,

I just cannot understand the desired result in your screenshots.  Share the download link of an Excel file with 2 tabs - input and output.  Write Excel formulas/prepare Pivot Tables there.  I will try to understand that logic and convert it into the DAX formula language.  

[vehloaaron]

@Ashish_Mathur I attached screen shots of the data model from the PBIX file, the screen shot of the excel and pivot are the desired results as I have described them.  I am not sure how to attach a file

[v-yifanw-msft]

Hi @vehloaaron ,

Based on the information you provide, you can use a combination of the CALCULATE , MAX, and FILTER functions to determine the latest marketing campaign response date that is less than or equal to each milestone date for each business opportunity. To better address your question, could you provide more data (without sensitive information) and your desired output.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vehloaaron]

Thank you and I appreciate your guidance @v-yifanw-msft I have attached a drop box link to PBIX file with sample data and a screen shot of a pivot chart I did in excel with expected outcome

[v-yifanw-msft]

Hi @vehloaaron ,

Thank you for your reply, unfortunately I can not open the link above, could you try providing a screenshot of the data in order for me to test and help you with this issue.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vehloaaron]

@v-yifanw-msft Sure, I attached screenshots in the main post