Rolling average with custom calendar slicer

[Kmur]

Hi all,

My report end users have asked me to create slicer for periods, where all graphs act to periods according to selected one (months, quarters, half year, year). They didn´t like the drill-down on each graph

I created table in Query based on my calendar table where I have all previously mentioned periods and calendar key as unique ID. Then unpivoted, connected to original calendar table and used this as a slicer. For my measures I´m using crossfilter and they all work nicely exept 12m rolling average. 

Here are my formulas:

Rolling 12 mth average =
IF (
ISBLANK(CALCULATE([Amount_TOTAL LY];DATEADD(Original_Calendar[CALENDAR_DATE]; 1;MONTH)));
BLANK();
(
CALCULATE (
[Amount_TOTAL LY];
DATESINPERIOD(
Original_Calendar[CALENDAR_DATE];
MAX(DATA_TABLE[TIME_PERIOD_DATE]);
13;MONTH
)
) - [Amount_TOTAL LY]
) / 12
)

Result of rolling average seems to calculate only the last part of the formula as my figures end up "-" but all should be positive. 

I have also tried to replace Original_Calendar with a date from "Period selectors" table but no result in months at all only in Q, HY & Y but also not correct. 

Measures used: (they work correct)

Amount_TOTAL LY =
CALCULATE([Amount_TOTAL];SAMEPERIODLASTYEAR(DISTINCT('Period selectors'[CALENDAR_DATE]));ALL('Period selectors'))

 Amount_TOTAL = CALCULATE(sum(DATA_TABLE[Amount]);CROSSFILTER(D_CALENDAR[CALENDAR_KEY];'Period selectors'[CALENDAR_KEY]; both))

"Period selectors" is the table I created to have a period slicer. 

Hope someone could help on this.

Thanks, Kertu

[v-chuncz-msft]

@Kmur,

You may try applying virtual relationship in DAX.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373