Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Cali_2020
Helper I
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

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!

View solution in original post

az38
Community Champion
Community Champion

@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


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

View solution in original post

5 REPLIES 5
az38
Community Champion
Community Champion

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

 


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

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
Community Champion
Community Champion

@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


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

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

az38
Community Champion
Community Champion

@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


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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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