Total of last 3 month without current month

[thians]

hi all, 
I need help to calculted the total sales in the last 3 months by selecting slicer week. with the rules. If I choose week 6, the total sales displayed are the previous 3 months, namely November 2021 to January 2022. 

How do I get the total value of last 3 months without months from the week slicer I chose. thank you

[MFelix · Accepted solution]

Hi @thians ,

When you refer that you want the last 3 months you can try something similar to this:

Totals Last 3 Months =
VAR FirstDateMonth =
    DATE ( YEAR ( MIN ( 'Calendar'[Date] ) ), MONTH ( MIN ( 'Calendar'[Date] ) ), 1 ) - 1
RETURN
    CALCULATE (
        [Measure],
        DATESINPERIOD ( 'Calendar'[Date], FirstDateMonth, -3, MONTH ),
        ALL ( 'Calendar'[Date] )
    )

[v-cgao-msft · Accepted solution]

Hi @thians ，

According to your description, I created two tables.

Relationship

Try these measure.

StartDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 3
VAR StartDate = EOMONTH(DateFrom,(numMonthsBack+1)*-1)+1
RETURN StartDate

EndDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 1
VAR EndDate = EOMONTH(DateFrom,numMonthsBack*-1)
RETURN EndDate

Total in last 3 Month = 
var _startDate = [StartDate]
var _endDate = [EndDate]
return
CALCULATE (
    SUM('Table'[Sales]),
    FILTER ( ALL('Table'), 'Table'[Date] >= _startDate && 'Table'[Date] <= _endDate )
)

The result should be like this.

Attaching the pbix file for reference.

Best Regards,

Community Support Team _ Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[MFelix · Accepted solution]

Hi @thians ,

When you refer that you want the last 3 months you can try something similar to this:

Totals Last 3 Months =
VAR FirstDateMonth =
    DATE ( YEAR ( MIN ( 'Calendar'[Date] ) ), MONTH ( MIN ( 'Calendar'[Date] ) ), 1 ) - 1
RETURN
    CALCULATE (
        [Measure],
        DATESINPERIOD ( 'Calendar'[Date], FirstDateMonth, -3, MONTH ),
        ALL ( 'Calendar'[Date] )
    )