topic Re: measure to look at specific forecast 3 months out to use in an equation? in DAX Commands and Tips

measure to look at specific forecast 3 months out to use in an equation?

Anonymous — Tue, 19 Oct 2021 14:32:40 GMT

OK, so I have some data in multiple tables. I need to create a measure that will allow me to capture the data from a column called qty in one table and filter it by the data in the date table and look at a dynamic result that is 3 months in the future. We will then take this result and use it as a denominator in an equation with data from a larger period to get a ratio. This needs to be dynamic, so that each month it updates and pushes further out in future

I'm having the hard time getting the data to be limited to the 3rd month in the future. We are currently in October, so the 3 month future is Nov, Dec, Jan. For the numerator I use a all 3 months of planned purchses, and devide by just the existing inventory of Jan.

Re: measure to look at specific forecast 3 months out to use in an equation?

Anonymous — Mon, 25 Oct 2021 06:13:06 GMT

Anonymous

Not very clear with your situation, if you can provide a sample table with expected output would help

For now, a typical method to get next 3 month value is using DATESINPERIOD() filter expression.

Measure = CALCULATE(SUM('Table'[Column]),DATESINPERIOD('Table'[Date],TODAY(),3,MONTH))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Re: measure to look at specific forecast 3 months out to use in an equation?

Anonymous — Wed, 27 Oct 2021 16:16:47 GMT

OK, so this works, kind of. I was able to have it return the next calendar month data. How can I get it to show me only the 2nd month inthe furture, or just the 3rd month in the future

1st Month = CALCULATE(SUM('PInv vs SFo'[QTY]),FILTER('PInv vs SFo','PInv vs SFo'[Functional Group] = "Net Forecast (Consensus with Consumption)"),DATESINPERIOD('PInv vs SFo'[CAL_DT],TODAY(),1,MONTH))

2nd month = CALCULATE(SUM('PInv vs SFo'[QTY]),FILTER('PInv vs SFo','PInv vs SFo'[Functional Group] = "Net Forecast (Consensus with Consumption)"),DATESINPERIOD('PInv vs SFo'[CAL_DT],NEXTMONTH(),1,MONTH))

Too few arguments were passed to the NEXTMONTH function. The minimum argument count for the function is 1.