I'm modeling an OLAP cube and I've created a measure in this way:
Measure1:
IDCustomer IDMonthYear Value
With the next values:
IDCustomer IDMonthYear Value
--------------------------------------
1 25 10
1 20 20
1 10 30
1 97 40
1 18 50
My time dimension is defined with 3 tables:
Year Month MonthYear
With the following records:
ID YEAR
------------
5 2018
0 2019
ID MONTH
----------------
5 JAN
1 FEB
0 MAR
4 APR
6 MAY
7 JUN
19 JUL
11 AGO
20 SEP
8 OCT
12 NOV
19 DIC
ID MONTHYEAR IDYEAR IDMONTH
----------------------------------------
25 JAN 2018 5 5
20 FEB 2018 5 1
10 MAR 2018 5 0
4 APR 2018 5 4
8 MAY 2018 5 6
9 JUN 2018 5 7
1 JUL 2018 5 19
11 AGO 2018 5 11
97 SEP 2018 5 20
0 OCT 2018 5 8
67 NOV 2018 5 12
18 DIC 2018 5 19
26 JAN 2019 0 5
21 FEB 2019 0 1
12 MAR 2019 0 0
5 APR 2019 0 4
13 MAY 2019 0 6
14 JUN 2019 0 7
2 JUL 2019 0 19
15 AGO 2019 0 11
38 SEP 2019 0 20
7 OCT 2019 0 8
68 NOV 2019 0 12
19 DIC 2019 0 19
So, this measure has the property "AggregateFunction" set to "LastNonEmpty" because I would like extract the value linked to the last month with a value, my expected result in a pivot table should be:
Customer1 DEC 2018 50
But I'm getting this:
Customer1 SEP 2018 40
I think because the system is taking the value associated to the biggest IDMonthYear...
Is there a way to have my expected result? I don't know very well MDX so I'm not sure if i need to put something in the "MeasureExpression" property.
You need to check the ordering of your months. I presume they are ordered on their text value. To check just drop them in cube browser and observe. If that be the case you need to fix the id column of your months table. Plus in the project go to your month dimension, for the month name the id should be the key column and in Orderby set the value key(all this will be done in properties window).