Rolling 12 Month Help

[Anonymous]

Thank you to all who reply.

I would like to see the rolling months from 2017 - 2021.  See below.  

Invoiced Sales rolling 12 mth =

CALCULATE(

[Invoiced Sales],

FILTER (

ALL('salesMaster'),

AND (

'salesMaster'[yearMonth] <= MAX ('salesMaster'[yearMonth]),

DATEADD (

'salesMaster'[yearMonth],

36,

MONTH

) > MAX ( 'salesMaster'[yearMonth] )

)

)

)

The first 2 years(2017,2018) worked perfectly but starting with 2/1/2019 it stopped. See tables below.

I also tried increasing the number of months. 

Any ideas?

Thank you,

BLD

**SOLVED - I used my Date table instead and the calcuations now work. 

My next question is there a way to use a slicer for the dates to see maybe midway thru one month to the next year. Example:  3/2017 to 2/2018??

[amitchandak · Accepted solution]

@Anonymous , select a date from date tbale, you should be get rolling data

example

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

but if you select one date and want to display 12 months on axis ,then you need independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +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