Historical formula result, use for most recent month

[michael_knight]

Hi,

I've got a problem that I need help with, I'll try and make it as understandable as possible.

We pay commission based on Conversion Rate (Leads divided by Deals), them deals can take months to Complete and they will get paid based on the convertion rate when it signed. I'll give an example below

This is showing the Leads (138) and the Deals (8) in the month of February, which is a Conversion Rate of 17.25

Convserion Rate Lower or equal to 15.00 = £400 per Deal

Convserion Rate Lower or equal to 19.99 = £300 per Deal

Convserion Rate Lower or equal to 22.99 = £200 per Deal

Convserion Rate Lower or equal to 25.00 = £100 per Deal

This is what they will get paid if the Deals complete based on the Conversion Rate. £2,400 in total and £300 per Deal

This is an exampe of a Completion

In this instance, when the Deal has Completed the Buyer will be due £300 in commission in April 2021 as one of the 8 Deals from February has completed

I'm having a really hard time with displaying this, I'm only able to display it within the context of the month. For Example the Completion example will use the convertion rate/commission due from April rather than February. There can be instances where the buyer will be getting commission from January, February, March etc, this isn't just based on one month.

I'm not sure if I need to create a column within the Table rather than using a Measure. I'm open to any idea

Let me know if theres any more information that is need

Here's the PBIX file: https://www.dropbox.com/s/kuw2lsr056bea9z/Commission%20Help.pbix?dl=0

Cheers,

Mike

[v-jingzhang]

Hi @michael_knight 

I don't understand the expected result and the logic when the buyer will be getting commission over several months. According to your pbix file, the measures [Created On], [Option Signed] and [Completion] are counting the date values in Sheet1 based on the context they are in. When you filter a month, they will calculate the counts in this month's context. 

What I'm confused is that in Sheet1 table, there are many duplicated rows with the same createdon/optionsignedDate/completionDate values or null values. Does every row represent a distinct Deal or there are historical data in the table (e.g. several rows represent different status for the same Deal on different dates)? Which rows represent that a buyer will be getting commission in multiple months and should have different rates on which months? Can you explain the expected result with some example?

Regards,
Community Support Team _ Jing

[michael_knight]

Hi @v-jingzhang 

The logic is that the buyer will be getting commission depending on how well they did when the deal was signed up. What you talked about in the first paragrah is the issue I'm having, if a deal is signed up in February and based on the conversion rate they got (Leads/Deals) then they will be due £150, this will be using the context from February but paying it upon Completion which could be months down the line

The reason why there is many duplicated rows is because CreateOn is showing the number of leads we got, we can get many in the same day. Each row shows a distict deal, even though it may appear to be duplicated. 

Here's an example from the dataset I provided

That is a snippet of the data for March. The conversion rate will be the CreatedOn column divided by the Option Signed Date total. 

This is the results from the Data I showed you. Based on the Conversion Rate, the commission will be £300 per option. That can get paid whenever, 6 months down the line, 12 months down the line etc. This is the issue I'm having, I need to pay the £300 in the context of March 2021, but I'm struggling

Hopefully that makes more sense, please let me know if it doesn't

Regards,

Michael