topic Next month forecast value in a measure in DAX Commands and Tips

Next month forecast value in a measure

aroberts27 — Mon, 10 Oct 2022 21:56:34 GMT

Hello,

I have a table that appends monthly forecast update versions together, each labeled in ascending order. For instance, Jan forecast is '202201', Feb is '202202', and so on.

I am trying to create a measure that both grabs the most recent forecast for all contexts (essentially filtering the table for the max version) AND to grab next month's value. So for instance, in the table below, I would want the "Next Month" measure to take the 202210 version and show the November forecast in the October column. I have tried a few versions of the formula pasted below, but the 'This Month' and 'Next Month' versions always match

Thanks!

Current formula:

Next Month =

VAR ForecastVersion = CALCULATE(MAX('2022ExceedraForecasts'[Version]), ALLSELECTED())

VAR Forecast = CALCULATE([ForecastCase], '2022ExceedraForecasts'[Version] = ForecastVersion)

Var NextMonthDate = DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

VAR NextMonthFcst = CALCULATE(Forecast, Filter('Date','Date'[Date] = NextMonthDate))

Return

NextMonthFcst

Current formula output:

Re: Next month forecast value in a measure

wdx223_Daniel — Tue, 11 Oct 2022 04:06:02 GMT

in dax, a variables is fixed after it is calculated, you put the Forecast in the 1st parameter of calculate, that will not change the value of Forecast. it always gives the value of CALCULATE([ForecastCase], '2022ExceedraForecasts'[Version] = ForecastVersion)

Re: Next month forecast value in a measure

aroberts27 — Wed, 12 Oct 2022 21:33:42 GMT

Ah thanks for your response

Follow-up question:

The table that appends monthly forecast update versions together (the one where I only need the most recent version, so 202210 right now) is pulled into my model from another Power BI dataset from my organization.

Can I pre-filter this table so only the version of the forecast I need is included in this model from the full table in the other model? This would eliminate the need for the calculation above. I can't use power query because its a Direct Query, but wondering if there is a workaround that would allow me to grab only the data I need.

Thanks!