# MDX - How to aggregate by date considering start and end date in a dimension

by Ricidleiv Tondatto   Last Updated July 03, 2017 20:06 PM

I need to calculate a measure grouping by date in a range of dates considering a start and end dates in a dimension. The objective is to count the number of students of a specific school that are actives on each day in a period.

The following SQL Code works on Data Warehouse (source of cube).

``````SELECT
t.[date]
,count(distinct A.SKStudent) as Students
FROM DM.DimTime t
CROSS JOIN DM.FactStudents a
INNER JOIN DM.DimStudents m on a.SKStudent = m.SKStudent
INNER JOIN DM.DimSchool e ON a.SKSchool = e.SKSchool
WHERE t.[date] between '20170502' and '20170512'
and e.SchoolCode = 123456
and t.[date] between m.[StartDate] and m.[EndtDate]
GROUP BY t.[data]
``````

This is what I did. I don't know how filter using the logic in CASE statement (between '???' marks):

``````WITH SET DateRange AS
[Dim Time].[Date].&[20170502]:[Dim Time].[Date].&[20170512]
MEMBER StudentsByDay AS
AGGREGATE(DateRange,
???CASE WHEN DateRange.CURRENTMEMBER
BETWEEN [Dim Students].[Start Date]
AND [Dim Students].[End Date]
THEN [Measures].[Students Count]
ELSE NULL END???)
SELECT
NON EMPTY { [StudentsByDay] } ON COLUMNS,
NON EMPTY { [DateRange] } ON ROWS
FROM [Education]
WHERE ( [Dim School].[School Code].&[123456] )
``````
Tags :