Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Predicted sales

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.

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    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.

11 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        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.