cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trebor_fire Frequent Visitor
Frequent Visitor

Sales forecasting on a per month basis using projected total revenues, start and end date

Hi everyone, 

 

I've been scratching my head and searching for quite a while now, so time for my first post. 

 

I have a calendar table with dates, yearmonth etc., and a project quotation table with estimated revenues, an estimated start date and end date. I've got an active relationship between Calendar[Date] and Project[Estimated Start Date] and an inactive relationship between Calendar[Date] and Project[Estimated End Date]. 

 

What I want to do is to show estimated revenues by YearMonth. I want to split the revenues evenly on all days. For example, say I have a project starting the 10th  of May, running for 100 days with an estimated revenue of 100 million, I want to see the following: 

 

                        May 2019           June 2019            July 2019             August 2019     

Project X             21 mill               30 mill                31 mill                   18 mill

 

Hopefully you brilliant minds can point me in the right direction, I am about to give up (not really, but frustrated atm). 

 

Thanks, 

Robert

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sales forecasting on a per month basis using projected total revenues, start and end date

hi, @trebor_fire 

You could use this way as below:

Step1:

Create a daily revenues column in QuotationTable

Project revenues/Day = DIVIDE(QuotationTable[Project revenues],DATEDIFF(QuotationTable[Estimated start date],QuotationTable[Estimated End date],DAY))

Step2:

Use this formula to add a new table

Table = 
FILTER (
    CROSSJOIN ( QuotationTable, 'Calendar' ),
    'Calendar'[Date] >= QuotationTable[Estimated start date]
        && 'Calendar'[Date] < QuotationTable[Estimated End date]
)

Step3:

Create a relationship between Calendar table with new table by Date filed.

Step4:

Now drag Year month from Calendar table, Quotation Name and Project revenues/Day from new table in a matrix visual.

Result:

1.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
AkhilAshok Established Member
Established Member

Re: Sales forecasting on a per month basis using projected total revenues, start and end date

Can you provide some sample dataset which can be copy pasted?

trebor_fire Frequent Visitor
Frequent Visitor

Re: Sales forecasting on a per month basis using projected total revenues, start and end date

Thanks for the reply. Example file available in dropbox: 

https://www.dropbox.com/s/nbfj0xr7hk7lgvt/Sales%20Forecasting.pbix?dl=0 

 

 

Community Support Team
Community Support Team

Re: Sales forecasting on a per month basis using projected total revenues, start and end date

hi, @trebor_fire 

You could use this way as below:

Step1:

Create a daily revenues column in QuotationTable

Project revenues/Day = DIVIDE(QuotationTable[Project revenues],DATEDIFF(QuotationTable[Estimated start date],QuotationTable[Estimated End date],DAY))

Step2:

Use this formula to add a new table

Table = 
FILTER (
    CROSSJOIN ( QuotationTable, 'Calendar' ),
    'Calendar'[Date] >= QuotationTable[Estimated start date]
        && 'Calendar'[Date] < QuotationTable[Estimated End date]
)

Step3:

Create a relationship between Calendar table with new table by Date filed.

Step4:

Now drag Year month from Calendar table, Quotation Name and Project revenues/Day from new table in a matrix visual.

Result:

1.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trebor_fire Frequent Visitor
Frequent Visitor

Re: Sales forecasting on a per month basis using projected total revenues, start and end date

@v-lili6-msft 

 

Thanks for the great help here! Several tricks here I didn't know about, and it worked like a charm.  

 

Robert