The problem arises when I'm looking at my cube for instance, at a month by month level in the hierarchy. If the last day of the month happened to be a day when there was no record, then PowerPlay puts in either a zero or an N/A in the cube cell. This gives misleading information...it should show me the last non empty value instead of just the last value. By the way, "Last Non Empty" aggregation function is provided by SSAS and IBM cubing service.
The solution is to make sure that the lowest level DAY in Time dimension only exists when there is data. In this case, date dimension is built as below
To achieve this goal, you need to do two things below:
- Create iqd for date dimension to only selected days when there is data
- Set it as default or include when needed in the level Year/Quarter/Month/Day in date dimension