I've been working in an OLAP cube as a pivottable in excel for a while now, but more recently I'm trying to integrate some calculated measures to streamline some things, and I've hit a wall.
The cube has date filters set up that specify the month & year (they get more specific, but finer levels aren't used).
The rows will list individual projects, and the values can reflect 2 measures: one that reflects the average score for the date range, and another that reflects the number of observations the average is based on.
I would like to create a calculated measure that will display the average score for each project as long as 2 criteria are met:
- There is a minimum of 100 observations for the whole date range
- there are no months with zero observations across the date range specified in the pivottable.
I should also clarify that the date ranges I use will vary and length, and will not always end with the most recent month, but they will always be in increments of whole months.
I'm part of the way there, as a calculated measure based on this will provide me with the average only if there are enough observations for the date range:
Now, I need to add the criteria of no months with zero observations.
I have attempted to use COUNT(), but in this form it ignores the date range set in the pivottable and returns a count of all of the months there is any value for the project, including zeros.
I tried determining the lowest number of observations in a month using this expression, but again it ignores the date range, and does not reflect empty cells:
I think CurrentMember is what I need to in