Rolling 12 Month Visual FAIL

[Anonymous]

There are many solutions posted regarding Rolling 12 Month averages,

I have taken advantage of these to calculate my own R12A requirements.

But I have Failed at creating a visual.

When using the computed measure for the averages and a calender table, I can populate a table with the month Year and the rolling average. All 12 numbers show up and the data is correct. The issue is that I have to use a date slider that is "In Between".  When I try to use a Drop down with Year/Quarter/Month hirearchy, which should indicate the end month of the rolling 12, I get only one row...the row of the selected end month.

I have seen this is another report which does work with the drop down but I unfortunately do not have access to the .pbix file so I can't see how they did it.

 I know it's something silly I have done/not done.

Thx

[amitchandak · Accepted solution]

@Anonymous , if you want use a slicer and then want 12 months on axis. Then either slicer or Axis should use disconnected on Inactive join tables

examples

//Date1 is independent/disconnected 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(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//DateInd is joined with Inactive join, Date is joined with Table with Active join.
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE( sum(sales[Gross Sales]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous · Accepted solution]

Hi,@amitchandak ,thanks for your concern about this issue.

Your answer is excellent!

As you suggested, a separate Date table needs to be created.

And I would like to share some additional details below.

Hi,@Anonymous 

1. First, you need to create a separate Date table.

2. Then, you need to create a measure to filter the data.

M_ =
VAR date_max =
    MAX ( 'Calander'[Date] )
VAR date_min =
    EDATE ( date_max, -12 ) + 1
RETURN
    IF (
        MAX ( 'Table_'[Date] ) >= date_min
            && MAX ( 'Table_'[Date] ) <= date_max,
        1,
        0
    )

3. Add Measure to Filter and finally you'll get the results you want. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , if you want use a slicer and then want 12 months on axis. Then either slicer or Axis should use disconnected on Inactive join tables

examples

//Date1 is independent/disconnected 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(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//DateInd is joined with Inactive join, Date is joined with Table with Active join.
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE( sum(sales[Gross Sales]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI