I am using Excel OLAP pivot using Analytics Services. The database has customer dimension with customer codes and time dimension with dates. Measures are Sales Price, which equals turnover. In OLAP Tools I created calculated member LW TY (last week this year) and a calculated measure CUSTOMER COUNT (which is supposed to calculate the number of customer codes for a given time period).
The MDX codes are here
LW TY
AGGREGATE(
LASTPERIODS( 7,
[Taxable Supply Dates].[Taxable Supply YQMD Hierarchy].[Taxable Supply Date Name].&[20220813]
)
)
And here is CUSTOMER COUNT
DISTINCTCOUNT(
EXISTING
[Customers].[Customer Code Hierarchy].[Customer Code]
)
The OLAP pivot returns values for calculated member LW TY correctly for measures (sales and orders) already existing in OLAP cube, but error for crossover between calculated member LW TY and calculated measure CUSTOMER COUNT.
[Excel result]
Please help.