Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Future Forecast Including Full Current Month

Hello! I am trying to display a future forecast cost for all future months including the full current month. Here is the closest I've found thus far:

Future_Forecast =
VAR LastActualsDate =
CALCULATE(
    MAX('IAT All Worker Timesheet Actual'[Date]),
    REMOVEFILTERS()
)
VAR FutureForecast =
    CALCULATE(
        [Forecast_Cost_SUM],
            KEEPFILTERS('Calendar'[Date] > LastActualsDate)
    )
VAR HoursResult = FutureForecast

RETURN
HoursResult
 
I am taking the last date in which there were actual hours or costs and displaying the forecast cost since that date.
However, this means I'm picking up some August actual hours/costs so the full August forecast isn't being shown.
See below for the current table result.
What I want is for the Future_Forecast column to match the current month(August) through end of year. I need this to continue each month too.
 

 

Any help is appreciated. Please ask any questions if something doesn't make sense. Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Try this
    These two measure will be used for the target calculation
    Sale sum = Sum('table'[sale])
    Condition =     CALCULATE((Table[sale sum]*(10/100)+Table[Sale Sum]))   
     // You can add percentage of your own choice here it is 10 percent // 

    Target value = CALCULATE([Condition],DATEADD(date_dim[Date],-1,YEAR),ALL(date_dim[Date],date_dim[Year],date_dim[Quarters],date_dim[Week No] ))+0




     

     

     

    Result should be like this

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Try this
    These two measure will be used for the target calculation
    Sale sum = Sum('table'[sale])
    Condition =     CALCULATE((Table[sale sum]*(10/100)+Table[Sale Sum]))   
     // You can add percentage of your own choice here it is 10 percent // 

    Target value = CALCULATE([Condition],DATEADD(date_dim[Date],-1,YEAR),ALL(date_dim[Date],date_dim[Year],date_dim[Quarters],date_dim[Week No] ))+0




     

     

     

    Result should be like this