cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Current forecast vs previous forecast

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!

2 ACCEPTED SOLUTIONS
Helper I

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:

Thanks again!

Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
5 REPLIES 5
Community Champion

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

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

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:

Thanks again!

Community Champion

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

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

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

Community Champion

thx! good luck, welcome to power bi world!

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors