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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!