MDX Calculated Measure - counts between start and end dates

2
votes

I have a dimension "Company" which has a member "CompanyName" and a measure group "CompanyMembership" which has measures "StartDate" and "EndDate". How do I count the number of companies that have membership for a date or month or year? I also have a user hierarchy Date.Calendar (like the one in Adventure Works).

If the date current member represents a month the member value will be the first day of the month and I only care if the membership covers that day. If a year the member value will be the first day of the year and I only care if the membership covers that day.

Here is some pseudocode for what I would like: sum( if (Measures.StartDate <= Date.Calendar.Value <= Measures.EndDate) then 1 else 0)

I would like to keep StartDate and EndDate in the same fact table row (not split into two rows - one with StartDate and one with EndDate) but would be willing to change the schema if there's no other way.

How can I write this expression?

1
ssasmdx

1 Answers

0
votes

I don't have a method for doing this in your current schema, but you should really consider making Membership a time dimension. That would make answering "was this company a member on this date?" much easier.