Current forecast vs previous forecast

[Cali_2020]

Hello all,

My apologies in advance if this has already been answered in a previous post but I haven't found a solution to what I am trying to do.

On a monthly basis, my team performs a forecast on the current quarter and up coming fiscal quarters and compares the forecast to the previous month's forecast in order to describe the variance of what is expected to occur to close the current and future forecasted quarters. The current equation that has been working until recently is the following:

=CALCULATE( [Forecast Revenue],LASTNONBLANK(Data[Forecast Date],([Forecast Revenue])))

However, what is happening is that data is being fed into our database for future months' forecast so the results are from the future forecasts. These forecasts are in the future and I had been trying to apply a type of variable or filter to apply the results of the most recent [Forecast Date] only of that forecast date that is in the past with "today()". However, after many different attempts with multiple compbinations of variables, conditional if clauses, and time intelligence scenarios I either  receive an error or the results are blank.

Does anyone have any suggestions on how to fix this or where I can look to resolve this? (And to solve the previous forecast result? 🙂 )

Thank you!

[Cali_2020 · Accepted solution]

Hi @az38 ,

This works great! Thank you! Just one more question, can I apply this measure to calculate the previous month's forecast? I tried the following but it returns the same results:

=CALCULATE([Forecast - Revenues],DATEADD(Data[Forecast Date],-1,MONTH))

Thanks again!

[az38]

Hi @Cali_2020 

try smth like measure

Measure = 
var _maxPrevDate = CALCULATE(MAX(Data[Forecast Date]),Data[Forecast Date]<TODAY())

RETURN
CALCULATE( [Forecast Revenue], Forecast Date=_maxPrevDate)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Cali_2020 · Accepted solution]

Hi @az38 ,

This works great! Thank you! Just one more question, can I apply this measure to calculate the previous month's forecast? I tried the following but it returns the same results:

=CALCULATE([Forecast - Revenues],DATEADD(Data[Forecast Date],-1,MONTH))

Thanks again!

[az38 · Accepted solution]

@Cali_2020 

try

var _maxPrevDate = CALCULATE(MAX(Data[Forecast Date]),Data[Forecast Date]<=EOMONTH(TODAY(),-1))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Cali_2020]

@az38 - worked like a charm! Thank you!!!

[az38]

@Cali_2020 

thx! good luck, welcome to power bi world!

do not hesitate to give a kudo to useful posts and mark solutions as solution