Predicted sales

[Apari]

We have an excel table where we have data of our sales opportunities. It includes dates of possible contract start date, contract length (in months), contract value and possibility of making the contract. We have x amount of rows and each row represents unique opportunity. Our sales team tries to predic our sales by month to month basis. They divide each of the contract values to contract months and they try to estimate the possibility percent. They then multiply the monthly value with that possiblity percent.  

How can I get these visualised so that it would show our predicted sales month to month. Example data as follows:

contract; start date; length; value; possibility

A; 1/1/2021; 12; 100 000€; 40%

B; 1/9/2020; 24; 1 500 000€; 15%

C; 1/10/2020; 18; 500 000; 30%

So how can I end up showing that in September our predicted sales are 9 375 € (1 500 000€ / 24 * 0,15), October to December 17 708,33€, every month of 2021 21 041,67€ etc.

[MFelix]

Hi @Apari ,

Create a disconnected calendar table then add the following measure:

Sales_Prediction = 
CALCULATE (
    SUMX (
        FILTER (
            Sales_Opportunities,
            Sales_Opportunities[Start Date] <= MAX ( 'Calendar'[Date] )
                && DATEADD ( Sales_Opportunities[Start Date], Sales_Opportunities[Length], MONTH )
                    <= MAX ( 'Calendar'[Date] )
        ),
        Sales_Opportunities[Value] / Sales_Opportunities[Length] * Sales_Opportunities[Possibility]
    )
) 

Check PBIX file attach.

[Apari]

Thanks, tho this didn't quite solve everything as in year 2022 it shows 21 041,67 for all months when it actually should be less since one contract ends in december 2021 and the rest end during the 2022 year. Any ideas how to work around that?

[MFelix]

Hi @Apari ,

You are correct I have made some confusion on the formula.

There is the need to add a calculated column with the end date:

End_Date = DATE(YEAR(Sales_Opportunities[Start Date]);MONTH(Sales_Opportunities[Start Date])+Sales_Opportunities[Length];DAY(Sales_Opportunities[Start Date]))

Now make the following measure:

Sales_Prediction = 
CALCULATE (
    SUMX (
   FILTER(ALL(Sales_Opportunities);
            Sales_Opportunities[Start Date] <= MAX( 'Calendar'[Date] )
                && Sales_Opportunities[End_Date]  >= MAX('Calendar'[Date]));
                
  
        Sales_Opportunities[Value] / Sales_Opportunities[Length] * Sales_Opportunities[Possibility]
    )
) + 0

Should work as expected.

[Apari]

It works, thanks. However I can't seem to make a slicer to sort between different contracts. 

I actually ended up making a new table where every contract got as many rows (months) added to them as the highest contract length was. If contract is shorter than the maximum length, then I just sort out the months over the contract length. Needed a bit of work but atleast it works as intended.

[MFelix]

Hi @Apari ,

What do you mean by:

However I can't seem to make a slicer to sort between different contracts. 

What is the way you want to slice, by contract ID, by length by date?

[Apari]

I mean that when I put a slicer to sort with different contracts and sorted to say contract A. It still showed the value of all the contracts combined, not the value of only the contract A.

[MFelix · Accepted solution]

Hi @Apari ,

You are correct sorry I made some test with the full table just to simplify and forgot to change one parameter you should use ALLSELECTED instead of ALL.

Formula below and updated file:

Sales_Prediction = 
CALCULATE (
    SUMX (
   FILTER(ALLSELECTED(Sales_Opportunities);
            Sales_Opportunities[Start Date] <= MAX( 'Calendar'[Date] )
                && Sales_Opportunities[End_Date]  >= MAX('Calendar'[Date]));
                
  
        Sales_Opportunities[Value] / Sales_Opportunities[Length] * Sales_Opportunities[Possibility]
    )
) + 0

Once again sorry for the misleading, you can also use other fields in the table to filter like the start date or end date.

[Apari]

Hi @MFelix, one more thing, it seems that for some reason the sum row stopped working in the table. Any ideas why?

[MFelix]

Hi @Apari ,

That as to do wit context, measure are calculated based on the information provided can be a slicer, a filter, a calculation, a column.

In the case of this measure the context is being given by the filtering of the dates so when you reach the total line the calculation is based on several dates and no only on a single value so the calculation is blank.

As you can see in  the file attach if you filter out the calendar the total line is based on the last selected date.

If you want to present the total of all the rows use the following syntax:

SUM_OnTOTAL =
IF (
    HASONEVALUE ( 'Calendar'[MMYYYY] );
    [Sales_Prediction];
    SUMX (
        SUMMARIZE ( 'Calendar'; 'Calendar'[MMYYYY]; "@Total_Sales"; [Sales_Prediction] );
        [@Total_Sales]
    )
)

I ussually use two measures to make it simple to edit if needed, but this can be made in a single measure.

Check PBIX file, I have place the two measures side by side.

[Apari]

Cool, thanks a lot 🙂

[Apari]

Hi, no need to apologize. Thanks for helping 🙂

This does seem to do the trick, thanks alot.