Projected Revenue for remainder of year

[ciken]

I am trying to build out a calculation that will give me the projected revenue for the rest of the year based on todays date and number of days in the year. I have been able to do this on a monthly scale (if we've sold $100,000 through the 10th and there are 30 days in the month my projected revenue should be $300,000). But when I try and recreate as an annual I'm lost. 

Below is my monthly projection DAX, I'd love help to build out the annual. 

Thanks

Monthly ECP Forecast =

CALCULATE(

DIVIDE([Total Revenue (ECP)],

DAY(MAX('Operations: Orders'[Order Date].[Date])) /*last day with sales*/

)

* DAY(EOMONTH(MAX('Operations: Orders'[Order Date].[Date]),0)), /*Days in Month*/

FILTER('Operations: Orders',EOMONTH('Operations: Orders'[Order Date].[Date],0)=EOMONTH(TODAY(),0) /*calculate the forecast for the current month*/

))

+

CALCULATE([Total Revenue (ECP)],

FILTER('Operations: Orders',EOMONTH('Operations: Orders'[Order Date].[Date],0)<EOMONTH(TODAY(),0))) /*Calculate the revenue for the past months*/

[Greg_Deckler · Accepted solution]

@ciken - You should be able to get the number of days left in the year via:

Days Left = (DATE(YEAR(TODAY(),12,31) - TODAY()) * 1.

[v-diye-msft]

Hi @ciken 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[amitchandak]

@ciken , One idea would be joining it with date table, get YTD daily and multiple it by 356

CALCULATE([Total Revenue (ECP)],DATESYTD('Date'[Date],"12/31"))/CALCULATE(distinctcount('Orders'[Order Date]),DATESYTD('Date'[Date],"12/31"))*365

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[ciken]

Thanks @amitchandak.

When I try this, the number I get is VERY small. Our revenue right now is somewhere around $5mm for this segment but when I apply your formula my number is $61,417 for the year. I've included my DAX of your formula below. Have I done something wrong?

Annual ECP Forecast =

CALCULATE([Total Revenue (ECP)],DATESYTD('Calendar'[Date],"12/31"))/CALCULATE(DISTINCTCOUNT('Operations: Orders'[Order Date]),DATESYTD('Calendar'[Date],"12/31"))*365

[Greg_Deckler · Accepted solution]

@ciken - You should be able to get the number of days left in the year via:

Days Left = (DATE(YEAR(TODAY(),12,31) - TODAY()) * 1.

[ciken · Accepted solution]

@Greg_Deckler , I used your formula below and added it to a last 30 day forecast. See below for all the formulas and the final product! Thanks for the help!!

Days Remaining in Year =

((DATE(YEAR(TODAY()),12,31)-TODAY())*1)+1

Total ECP Revenue (30 day rolling) =

CALCULATE([Total Revenue (ECP)],DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-30,DAY))

Annual ECP Projection =

var Remainingforecast = CALCULATE(([Total ECP Revenue (30 day rolling)]/30)*[Days Remaining in Year])

var annualrevenue = Calculate([Total Revenue (ECP)],FILTER('Operations: Orders','Operations: Orders'[Order Date].[Year]=YEAR(TODAY())))

Return

Calculate(Remainingforecast+annualrevenue)

[ciken]

@Greg_Deckler , I'm not sure how knowing how many days left helps me. I need to figure out an average daily revenue through yesterday and apply it to the full year. 

So, if our revenue through August 31 was $5mm I should be able to project (5mm / 243 days = average of $20,576.13 per day). 

Multiply $20,576.13 by 365 days = $7,510,288.07 for an annual projection. 

How can I write that?

[Greg_Deckler]

@ciken - I misunderstood but it's the same concept. However, I need to see sample data because I don't know what you have in terms of what you are working with.