Forum Discussion
Adjusting cross filtering between visualisations
PowerWhy , if the duration in selection is more than 12 month
This can give 12 month of trend
Rolling 12 Sales =
var _max = maxx(allselected(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))
and this rolling 12
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))
But if the selection is less than 12 months you can only get rolling 12 months data using
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))
But you will not be able to see the trend of 12 months, for that, you need an independent table for the slicer
//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
- PowerWhy3 years agoHelper IV
Thanks amitchandak but the graph should show each monthly position (for 12 months), rather than a 12 month average. Does that make sense? Thanks