Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling 12 Month Help

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??
 
 
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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