How to get all items created or still open for a given time

Question

I work with the system, the elements are created ( postDatedimension) and closed ( endDatedimension). The column is endDatealways filled when the item was last viewed. An item is considered closed at a specific time if its last seen date is before the date you request. Each row in the fact table has an element postDate, endDate, locationIDand some of the other measurements used for aggregation. What I'm trying to accomplish is all the elements that are still active for a certain period of time. For example, I want to know all the items published in November 2008 or before November 2008 that are not yet closed. In SQL, it will look something like this:

SELECT C.geoCountyArea,TM.CalendarYear,COUNT(DISTINCT a.itemid)
FROM [dbo].[factTable] a
JOIN dbo.dimDate AS TM
    ON TM.DateKey BETWEEN postDate AND endDate
JOIN [dbo].[dim_geography] C
    ON A.geographyID=C.geographyID
WHERE C.geoCountyArea = '1204000057'
AND TM.CalendarYear = 2008 AND TM.MonthNumberOfYear = 11
GROUP BY C.geoCountyArea,TM.CalendarYear
ORDER BY C.geoCountyArea,TM.CalendarYear

This returns 27,715which is expected. Now in MDX it looks like this:

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE(
        {NULL:[PostDate].[Month Name].&[2008]&[11]} * {[EndDate].[Month Name].&[2008]&[11]:NULL},
        [Measures].[Fact_itemCount]
    )

SELECT NON EMPTY (
    Measures.[itemCount]
    ) ON 0,
NON EMPTY (
    {[PostDate].[Month Name].&[2008]&[11]},
    {[Geography].[Geo County Area].&[1204000057]}
    )ON 1
FROM [Cube];

This returns 27,717- which is 2 more than the SQL version, which can be called by elements without an end date. Now the difficulty comes when I want to get more than one explicit time - for example, the number of elements for all months in 2008 or the number of elements for all years. I searched for methods to associate this parameter with another using the video playback options and came across this link. I changed my script to look like this:

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE( 
          {NULL:LINKMEMBER([DATE].[Calendar].CURRENTMEMBER
                     ,[PostDate].[Calendar])}
        * {LINKMEMBER([DATE].[Calendar].CURRENTMEMBER
                    , [EndDate].[Calendar]):NULL}
        , [Measures].[Fact_itemCount]
        )

SELECT {Measures.[jobCount]} ON 0,
NON EMPTY (
    {[DATE].[Month Name].&[2008]&[11]},
    {[Geography].[Geo County Area].&[1204000057]}
    )ON 1
FROM [Cube];

This, however, only returns items created in November 2008 - value 14,884. If I add in other months, I get individual calculations for each month, but again, these are just objects created for these months.

"" // AGGREGATE?

Answer 1

NonEmpty?

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE( 
          {NULL:
             NONEMPTY(
               [PostDate].[Month Name].MEMBERS //<<AMEND TO EXACT STRUCTURE USED IN YOUR CUBE
              ,[DATE].[Calendar].CURRENTMEMBER
             ).ITEM(0).ITEM(0)}
        * {NONEMPTY(
               [EndDate].[Month Name].MEMBERS //<<AMEND TO EXACT STRUCTURE USED IN YOUR CUBE
              ,[DATE].[Calendar].CURRENTMEMBER
             ).ITEM(0).ITEM(0): NULL}
        , [Measures].[Fact_itemCount]
        )
...

Answer 2

, ( SQL ):

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE(
        {NULL:LINKMEMBER([Post Date].[Calendar],
            [Post Date].[Calendar])}
        * {LINKMEMBER([Post Date].[Calendar],
            [End Date].[Calendar]):NULL},
        [Measures].[Fact_itemCount]
    )

SELECT {Measures.[itemCount]} ON 0,
NON EMPTY (
    {[Post Date].[Month Name].Children},
    {[Geography].[Geo County Area].&[1204000057]}
    )
FROM [Cube]

, LINKMEMBER , .