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

[Anonymous]

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

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

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:

and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

@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]

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

[Anonymous]

Thanks for the reply. Example file available in dropbox: 

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