cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidCameron
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@DavidCameron 

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.

View solution in original post

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@DavidCameron 

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.

View solution in original post

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.
 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors