Past 3 month excluding the current month.

[Easley06]

I have scenario where I have to filter sales for last 3 months based on current Month.

Ex: If Selected Month is March which has maximum date 11th (11-Mar-2021) . I'll need to calculate for Last 3 months excluding current Month i.e. neet to get the vaule of Feb 2021 ,Jan 2021,Dec 2020.

current formula : 

P3  = (CALCULATE(SUM(FACT_OFF_TAKE[GROSS_AMT]), DATESINPERIOD(DATE_SYSTEM[DATE], LASTDATE(DATE_SYSTEM[DATE]), -3,MONTH),ALL(DATE_SYSTEM[MONTH_NAME],DATE_SYSTEM[MONTH],DATE_SYSTEM[YEAR])))/3

[tamerj1 · Accepted solution]

Hi @Easley06 
Please try

P3 =
VAR CurrentDate =
    MAX ( DATE_SYSTEM[DATE] )
VAR EndDate =
    EOMONTH ( CurrentDate, - 1 )
VAR StartDate =
    EOMONTH ( CurrentDate, - 5 ) + 1
RETURN
    CALCULATE (
        SUM ( FACT_OFF_TAKE[GROSS_AMT] ),
        DATE_SYSTEM[DATE] >= StartDate,
        DATE_SYSTEM[DATE] <= EndDate,
        ALL ( DATE_SYSTEM[MONTH_NAME], DATE_SYSTEM[MONTH], DATE_SYSTEM[YEAR] )
    ) / 3