cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trebor_fire
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
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

@v-lili6-msft 

 

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

 

Robert

AkhilAshok
Solution Sage
Solution Sage

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

Thanks for the reply. Example file available in dropbox: 

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

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.