Retrieving the absolute last entry from set of members in a level
Hi,
I have a persistent class “sp.SensorReading” which has a number of Properties: Date, SensorName, SensorReading. (sometimes multiple readings from the same sensor, on a given day)
...
Temp 28 Jan 33.5
Temp 29 Jan 31.2
Temp 30 Jan 33.1
Temp 30 Jan 34.1
Temp 31 Jan 32.1
Temp 31 Jan 33.1
Say I built a cube built to analyse data from this persistent class. With a simple date dimension, and SensoRName filter, I can show the default COUNT of readings for a given month/year:
Jan-2015 94
Feb-2015 54
Mar-2015 78
…
I have a date dimension with levels for Year, Quarter, MonthYear, and Date. Instead of the default COUNT measure , I want to get the last value pushed out by a sensor for a given selected period. So let’s say, the last value for temperature, for each date dimension member: This is what I would want to see if selecting on MonthYear:
Jan-2015 33.1
Feb-2015 32.2
Mar-2015 31.8
…
My approach is be to define a calculated measure which, returns the reading from the last member of the selected group of readings, using the MDX %LAST function.
Something like the following defined in the cube - Calculated Measure: LastReading:
However – As in the example for January above, sometimes, I get multiple readings on the same day for the same sensor, and if that happens to be the last day of the month, the reading are added.
Jan-2015 65.2
Feb-2015 32.2
Mar-2015 31.8
Is it possible to have a pure MDX - based expression to fix this, ie, when %LAST(...Members) actually returns multiple entries ?
(ie, Can I achieve what I want without introducing a cube dimension that is resolved based on whether a particular reading is the last reading of the month, at data upload or build – because – the period is not always defined as ‘month’, but could be Year, YearQuarter, or even arbitrary dates.
thanks -