How to create a matrix with values that are less than or equal to the column value

[VimWan]

Hi Everyone,

I'm quite a novice with Power Bi and am having an issue figuring out an efficient way to complete the following:

I have 2 tables, one that contains Student Enrollment data and a table I've created which contains the reporting dates of the report I want populated. I've createad an example below:

Student enrollment data:

stdnt #	program	add date	drop date
1	accounting	1/20/2022
2	human resources	3/15/2022
3	accounting	2/22/2022	2/25/2022

Report dates:

report dates

1/25/2022

3/16/2022

4/25/2022

What I want in Power Bi, is for my table to look like the attachment below, where the report dates are the column headers and the counts of all enrollment records for each program that have an Add date that is less than or equal to the report date for each program is listed in the body of the table where the values would go.

Thanks for your time and help with this.

[MarkLaf]

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:

[lbendlin]

that seems like a straightforward crossjoin.  What have you tried and where are you stuck?

[VimWan]

Thanks Ibendin,

I understand that a crossjoin mashes all the rows from both table together, is there a way for us to specify that we want to mash specific records from the student enrollment data to every consecutive date record where the add date of the student enrollment table record must be less than or equal to the report date? 

[VimWan]

Right now from your suggestion, I'm thinking I can crossjoin all rows from both tables and then remove any rows where the Add Date > Report Date after the join. Since this seems inefficient, any suggestions to improve this?

Another point I wanted to make is that if my student enrollment table is a table with a large number of rows, wouldn't using a cross join potentially cause performance issues?  

[lbendlin]

Since this seems inefficient

all database engines are optimized for handling cartesian products. They have to do that anyway, for every single join.  you can help by reducing cardinality soon in the process.