Calculate purchases in a month 9 months forward from today

[bwhitlock]

Im having an issue with my measure when it crosses years. Im trying to calculate my total purchase in a month 9 month from this month. I can calculate 2 or 3 months forward but when it transitions across years mess it up... How do I figure out what the date range should be in the month 9 months from this month?

Thanks all in advance

[amitchandak]

Like this

Measure =
var _max = today()
var _min = date(year(_max),month(_max)+9,day(_max))
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

or
Measure =
var _max = eomonth(today(),-1)+1
var _min = eomonth(date(year(_max),month(_max)+9,1) ,0)
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

or

Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = date(year(_max),month(_max)+9,day(_max))
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

[Ashish_Mathur]

Hi,

Try this measure

=Calculate([your_measure],datesbetween(Calendar[Date],Edate(Eomonth(Today(),-1)+1),9),Edate(Eomonth(Today(),0),9)))

Hope this helps.

[bwhitlock]

Brilliant thank you so much. Had to make a slight adjustment as I think you have an extra parenth

Calculate([Tot Open PO Each Qty],datesbetween('Calendar Table'[Date],Edate(Eomonth(Today(),-1)+1,9),Edate(Eomonth(Today(),0),9)))

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[mahoneypat]

Have you tried DATEADD()?  This assumes you have a date calendar named Calendar and it is Marked as Date Table.

Measure = Calculate([YourMeasure], DATEADD(Calendar[Date], 9, month))

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat