Adding 2 months to a month selected via a slicer

[LucieM]

Hello, I am trying to write a measure that calculates an average monthly value per team based on a month slicer.

Resource Situation Actual N+2 =

IF (

CALCULATE (

DISTINCTCOUNT ( 'Date'[Month] ),

ALLEXCEPT ( 'Date', 'Date'[Month] )

)

= CALCULATE ( DISTINCTCOUNT ( 'Date'[Month] ), ALL ( 'Date' ) ),

CALCULATE (

SUM ( 'Table'[Resource need %] ),

FILTER ( 'Table', 'Table'[Month Number] = MONTH ( TODAY ()) + 2 )

),

CALCULATE ( SUM ( 'Table'[Resource need %] ) , ALLEXCEPT('Table', 'Table'[Team name]),

FILTER ( 'Table','Table'[Month Number] = MAX('Table'[Month Number] + 2 ))

 ))

Hello, I am trying to write a measure that calculates an average monthly value per team based on a month slicer.

I have 2 tables in my model : a date table,  and  my main table consisting of the following columns : project name, departement, month (date of the start of the month), month number, and the share of FTE needed from this project and this month. We have dates covering several years in the source file.

The twist here is that I want an offset in this measure, showing the value for 2 months after the selected month.

There is an IF statement because if we don't select any month from the slicer, we want to display the value for the current month + 2. If we do select a month from the month slicer, I want the value for 2 months after this selection.

I have tried different version of this line

FILTER ( 'Table','Table'[Month Number] = MAX('Table'[Month Number] + 2 ))

By using DATEADD or EDATE, but none of this worked

Any idea on how to make this work ?

Thank you in advance

[amitchandak]

@LucieM , try a measure like

=
var _min =Minx('Date','Date'[Date])
var _max = eomonth(_min,2) //2 or -2 based on need
return
CALCULATE('Table'[Sales], Filter(Date,'Date'[Date] >=_min && 'Date'[Date] <=_max ))