I have a Client dimension with a CreationDate and CloseDate. I also have a fact table with a SnapshotDate (last day of month), ClientID and multiple measures.
I would be interested in getting the number of new clients and closed clients during the month on the same bar chart. I need a common date (I thought SnapshotDate) and the count of clients having the same month in both creation and close dates fields, but I cannot figure out how to write that using set analysis.
My bad. The model changed since the original post.
MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate is now [MonthlySnapshotDate.FirstDayOfMonthDate]. We used the derived fields initially, and reverted to the original calendar table due to some issues with set analysis.
The are no synth keys since we delete the original MasterCalendar table. We only keep the copies and the fields are prefixed with the table name.
I tried to build a sample app, but I was not able to reproduce the issue in the smaller dataset. I guess the problem is probably somewhere in my app, not the script proposed by Gysbert Wassenaar.