Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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.

View solution in original post

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

@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.
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors