Use of XRANGE to Calculate a Date Range
You use @XRANGE
to produce a range of dates in a business rule. For
example, the following code creates a range of members between Mar in FY22 and Feb in
Y23.
Member = @SUMRANGE("Actual"->"Final"->"Plan Total",@XRANGE("FY22"->"Mar","FY23"->"Feb"))/12;
The Year dimension, generally, is a flat dimension with only level0 members all of which
are years, for example, FY22
and FY23
.
The Period dimension, however, may contain other level0 members after Dec (assuming a
Jan:Dec
year). These will also be included in an
XRANGE
even though you do not want to include them in your
calculation. In such a scenario, use two XRANGE
calculations, one for
each year. For example:
"Member" = (@SUMRANGE("Actual"->"Final",@XRANGE("FY22"->"Mar","FY23"->"Dec"))+
@SUMRANGE("Actual"->"Final",@XRANGE("FY23"->"Jan","FY23"->"Feb")))/12;
Using this method ensures that the XRANGE
stays within the
Jan:Feb
scope of the Period dimension.