Aggregates in Periodic Snapshot Fact Table

I have a periodic snapshot fact table that captures the number of customers who joined the membership club in a particular reporting period.

In essence, it looks like:

FactKey	CustKey	JoinDT_Key	ReportingDT_Key	Count	NewMemberCount	ExistingMemberCount
21	1	88	88	1	1	0
22	2	88	88	1	1	0
23	3	88	88	1	1	0
24	1	88	89	1	0	1
25	2	88	89	1	0	1
26	3	88	89	1	0	1

Questions:
1.) I made this to a cube in Analysis Services, and when users pull up the cube in Excel (pivot), I don't want it to SUM incorrectly.
E.g.,
For ReportingDT_Key = 88, the count is 3
For ReportingDT_Key = 89, the count is again 3
But it shouldn't sum up 3+3 in the pivot table.

This is similar to inventory as of a certain period in time, but how do you present this to users so it doesn't get incorrectly summed?

2.) Is it right to put NewMemberCount and ExistingMemberCount like that?
The problem I am having with the above is that the number is always going to be separate for both of those and I could not have a single graph that shows total is 3 members and breakdown of which are that: X are new and Y are existing members.

Any help you can provide would be much appreciated.

What you have are semi-additive measures. That is to say measures that are not additive across all dimensions. In this case, they are not additive across time.

This is fairly typical for periodic snapshot tables. Some BI tools allow you to control how a user can aggregate such measures, others do not. In the latter case, it becomes a matter of educating the users.

SSAS allows you to specify AggregateFunction (eg. LastNonEmpty) for semi-additive measures so that additive aggregation happens on all others but date dimension. Make sure you set the date dimension type to “Time”

I looked at SSAS' AggregateFunction (LastNonEmpty), but I am not sure if that would work for me though.

Notice that I need it summed across the Date Dimension but only for each ReportDT_Key:
E.g.,
For ReportingDT_Key = 88, the count is 3 (SUM of COUNT field)
For ReportingDT_Key = 89, the count is again 3 (SUM of COUNT field)

But I don't want it to have a Grand Total/Sum to show that I have 6 because these are snapshots.
So in Excel pivot table, it by default would try to sum it up and show a "Grand Total" of 6.
I can right-click the column/row and click "Remove Grand Total" but I just thought if there's a way to actually suppress it so none of the users would have to see it.

So in summary, it is doing a SUM across the time dimension but only for same attribute values (e.g., same ReportingDT_Key) but should NOT SUM for all (Grand Total).

Let me know if this is at all possible. Thanks so much for your help.

If you took JoinDT_Key out of your fact table so you are not role playing with the date dimenson I think you would get correct results in SSAS using the LastNonEmpty or LastChild aggregate function for your measures. (I use LastChild with my Inventory daily snapshot fact table.)

A periodic snapshot doesn't typically have an additional date/time dimension reference in it because the time dimension is represented by the frequency of the snapshot. JoinDT_Key would make more sense in a separate transactional fact table (or possibly as an attribute in the Customer dimension.)