Forum Discussion
Current month forecast
Hi,
I have a consolidated forecast table that has SF month - the month the forecast was done, month - the month of the forecast & forecats QTY.
Ex;
SF month - Jan 2020 , Month - Dec 2020, - this is the line with the forecast done in Jan for Dec
SF month Feb 2020, Month - Dec 2020, this is the line with the forecast done in Feb for Dec
I want to create a measure / report that gives me the latest forecast, so If I have SF month for April , give me SF month April othewise give me SF March and so on.
How do I do this?
Perhaps:
Measure = VAR __Forecast = CALCULATE(sum('Forecast Consol'[Forecast QTY]),USERELATIONSHIP('Forecast Consol'[SF Month],'Dates Slicer'[DateKey]))) VAR __LastMonth = <calculate what your last month's date is, not entirely sure how to do this with the information provided. If you had an actual date field, this would be relatively easy> RETURN IF( ISBLANK(__Forecast), CALCULATE(sum('Forecast Consol'[Forecast QTY]),'Forecast Consol'[SF Month]=__LastMonth), __Forecast )
9 Replies
Chedva ,
Not very clear, but you might need something like this
Measure = var _max = format(today(),"MMM YYYY") var _max1 = format(date(year(today()),month(today())-1,day(today())),"MMM YYYY") var _cnt = countrows(forecast, forecast[month]=_max) return if(_cnt > 0,calculate([forecast],forecast[month]=_max) ,calculate([forecast],forecast[month]=_max1))Greg_Deckler amitchandak thanks for your help.
I will clarify.
I have a table called consol forecast (this forecast is consolidated from a few different resources)
In the report section I desplay the following table (I added for each column from what table it is taken)
In the filter I chose the current month display the latest ver of the forecast. ex; I chose March 2020, this is the report I will see:
Region
(Source - lookup table related to Forecast Consol)
SF month
(source - Date slicer table - related to Forecast on SF month column)Month
(source - Date table - related to Forecast on month column)
Forecast cons QTY -
Measure = CALCULATE(sum('Forecast Consol'[Forecast QTY]),USERELATIONSHIP('Forecast Consol'[SF Month],'Dates Slicer'[DateKey])))A
March 2020
March 2020
XX
B
March 202
March 2020
YY
A
March 2020
April 2020
ZZ
B
March 2020
April 2020
NN
I want to be able to choose March 2020, but if there isn't a SF forecast for one of the regions for March 2020, so show me the Feb 2020 version.
Perhaps:
Measure = VAR __Forecast = CALCULATE(sum('Forecast Consol'[Forecast QTY]),USERELATIONSHIP('Forecast Consol'[SF Month],'Dates Slicer'[DateKey]))) VAR __LastMonth = <calculate what your last month's date is, not entirely sure how to do this with the information provided. If you had an actual date field, this would be relatively easy> RETURN IF( ISBLANK(__Forecast), CALCULATE(sum('Forecast Consol'[Forecast QTY]),'Forecast Consol'[SF Month]=__LastMonth), __Forecast )
- You really need a Date in your source data, can you share sample source data beyond what you have shown?
