Rolling 12 months Showing values when nothing is selected in Date slicer

[kristel_tulio]

Hi I would like to ask your help. I want to calculate the Rolling 12 months potential revenue. I already have the calculation but when nothing is selected on my year slicer the values return blank. I want to show alll values in potential revenue based on all the years on my date slicer that is in the view.

Here's my calc:

Revenue rolling 12 month =
IF(
SUM(ServiceName[Potential Revenue]) <> BLANK(),
var _lastvisibledate = LASTDATE(Dates[Date])
var _canmove12mback = NOT ISBLANK( DATEADD( _lastvisibledate, -1, YEAR) )
var _result =
CALCULATE(
SUM(ServiceName[Potential Revenue]),
DATESINPERIOD(
Dates[Date],
_lastvisibledate,
-1,
YEAR
)
)
return
IF(_canmove12mback, _result)
)

[kristel_tulio]

Hi @amitchandak 

I tried the calculation but it seems it's not working as rolling 12 months. I'm not sure if I've used it right

[kristel_tulio]

Hi @amitchandak 

the card still show blank values when date slicer is unselected when I used the maxx. The today() function works but the other year selection will show blank

[amitchandak]

@kristel_tulio , Try like

rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1
BLANK())
return
CALCULATE([net] ,filter('Date', 'Date'[Date]>= _min && 'Date'[Date]<= _max))

But if your selection is less than 12 months and you need 12 months on axis, You need independent table on slcier

//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))

[amitchandak · Accepted solution]

@kristel_tulio , Try like

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))

or

Rolling 12 Sales =
var _max = today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))