Retrieving the absolute last entry from set of members in a level
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:
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:
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.
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.