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
