Measure: Last X Months and Next X Months by selected date

[thhmez7]

Hello,

I'd like to create a measure that displays the revenue values of the last and next month by a selected date, in this case a MonthYear is selected. For example, if 04.2021 is selected, then the chart should show values of March, April and May 2021.

I've been trying to include Dateadd in Selectedvalue(Date).

Calculate( Sum([Revenue])

, Date <= Selectedvalue(Date) .... 

Thanks a lot.

[thhmez7]

Thank you, eomonth exporession helps a lot.

[amitchandak · Accepted solution]

@thhmez7 , If you want to show the rolling value not the trend

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),1),-3,MONTH))

But if need trend, you need an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = eomonth(maxx(allselected(Date1),Date1[Date]),1)
var _min = eomonth(_max, -3) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI