Forum Discussion
How to create a matrix with values that are less than or equal to the column value
Do you want a YTD count of enrollment at the specified report dates? If yes, you can achieve this by setting up a date table and YTD measure. Even if the below YTD calculation isn't exactly what you want, I suspect the general approach may still work for you even if you have to modify the measure in #3 to get exactly what you want.
Details. Given tables names 'StudentEnrollment' and 'ReportDates' for your data provided:
1) Create date table. There are lots of robust methods, but here is some very quick DAX just to get the visual working:
Dates =
VAR minDt = DATE( YEAR( MIN( MIN( ReportDates[report dates] ), MIN( StudentEnrollment[add date] ) ) ), 1, 1 )
VAR maxDt = DATE( YEAR( MAX( MAX( ReportDates[report dates] ), MAX( StudentEnrollment[add date] ) ) ), 12, 31 )
RETURN
CALENDAR( minDt, maxDt )
2) Set up relationships between your two tables and Dates ( StudentEnrollment <--M:1-- Dates --1:M--> ReportDates):
3) Create the measure you want for counting enrollment:
EnrollmentCount =
CALCULATE(
COUNTROWS( StudentEnrollment ),
CALCULATETABLE( DATESYTD( Dates[Date] ), ReportDates )
)
4) Construct the visual you want - from your screenshot, it look like you want a matrix: