Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |