Forum Discussion
Calculating sales forecast based on current sales
In my dashboard, I am trying to show what the projected sales are for this year based on actual sales and remaining forecast. For example, for any activity through April 2017, I would add up the fore...
This might work:
Forecast Balance = VAR CurrentDate = CALCULATE ( LASTNONBLANK ( autoCalendar[Date], [Total Sales] ), ALL ( autoCalendar[Year-Month] ) ) RETURN CALCULATE ( SUM ( Forecast[Forecast] ), FILTER ( VALUES ( Forecast[Date] ), Forecast[Forecast_Date] > CurrentDate ) )This Year Sales = VAR CurrentDate = CALCULATE ( LASTNONBLANK ( autoCalendar[Date], [Total Sales] ), ALL ( autoCalendar[Year-Month] ) ) RETURN TOTALYTD ( SUM ( sales[Total_Sales] ), CurrentDate )then finally
Project sales = [This Year Sales]+[Forecast Balance]
EDIT: Thinking about it, since the only slicer on page seems to be year, you can reduce the CurrentDate VAR to just:
CurrentDate = LASTNONBLANK ( autoCalenar[Date], [Total Sales] )
assuming
Total Sales = SUM ( sales[Total_Sales] )
