Minimum value for measure calculated daily across year

[crispybits77]

Hi all

Back with another school attendance query. I have a fact table with studentID, date, IsPresent and IsPossible columns. IsPresent and IsPossible are 1s for days when the student was present / was supposed to attend and 0s when the student was absent / school was not open.

I have a nice simple measure where I just divide the sum of IsPresent with the sum of IsPossible to get the current attendance percentage for any student or group of students in visuals. All good all working fine.

We also have a metric to track called "persistent absence". This means a student has less than 90% attendance. I can work this out fine for the current data, it's just if [Att%]<0.9. Easy peasy.

I've also been asked for a historical "Has been persistent absent this year" flag, so we can track kids who have fallen below the threshold and then improved again, and also analyse what kinds of interventions work best for helping kids who are most likely to be absent attend regularly. This is where I'm struggling. I've tried writing various types of measures but none of them work the way I want.

Slight complication, we don't want to count anything in the first 28 days as 1-2 days absent in that first few weeks will potentially flag kids that are otherwise attending fine but just had a sickness bug or planned medical appointment or whatever at the start of term.

I have an Day_Order column in the date table that gives each day in the school year a number 1-365 starting on the first day in September each year. I thought what I should be doing is creating a table variable of the student's [Att%] for each day in the current year and taking a MIN of that [Att%] column whan DayOrder > 28. Sounds simple enough...

However when I tried creating a new calculated table with distinct student IDs repeated 337 times with 29-365 in a second column and then use the divide calculation with a filter for dayorder < day number in row but even with 32GB of RAM (over 20GB spare when I start the calculation) I can't calculate more than about 50 days at a time without maxing out RAM usage - I know I could do that in multiple tables instead and then join all the tables together and take the MIN from that but it seems like I'm missing something easier?

Can anyone give me a little pointer please? I'm thinking it's probably be a MINX and ridiculously simple but I'm bashing my head against a brick wall now...

[crispybits77]

Not sure yet - been diverted to work on something else will update once I get time to work on it again (I'll need to come back here to remind myself how to solve it anyway)

[v-tangjie-msft]

Hi @crispybits77 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[crispybits77]

Thanks I'll give it a go and come back and mark as solution if I can get it working from this, or post my specific DAX if I still need more help.

[CoreyP]

@crispybits77 , I thought this was interesting so I took a stab at it. 

I built the model like so:

My sample data like so:

I wanted to use a date table and not the "isPossible" column from your attendance table, so here's what I added to my date table:

isWeekday = IF( WEEKDAY( 'dim Date'[Date] , 2 ) < 6 , TRUE() , FALSE() )

School Day of Year = IF( 'dim Date'[isWeekday] = FALSE(), BLANK() , RANKX( FILTER( ALL( 'dim Date' ) , 'dim Date'[isWeekday] = TRUE() ) , 'dim Date'[Date] , , ASC ) )

Then I built some measures:

28 Day Grace = SUMX( FILTER( ALL( 'dim Date' ) , 'dim Date'[School Day of Year] = 28 ) , 'dim Date'[Date] )

---------------------------------------------------------

Cumulative School Days = MAXX( FILTER( ALL('dim Date') , 'dim Date'[Date] <= MAX( Attendance[date] ) ) , 'dim Date'[School Day of Year] )

---------------------------------------------------------

Cumulative Attendance Actual = 
VAR _date = MAX( Attendance[date] )
VAR _student = MAX( Attendance[studentID])
VAR _calc = SUMX( FILTER( ALL( Attendance ) , Attendance[date] <= _date && Attendance[studentID] = _student ) , Attendance[isPresent] ) 


RETURN
_calc 

---------------------------------------------------------

Attendance Rate Actual = DIVIDE( [Cumulative Attendance Actual] , [Cumulative School Days] , BLANK() ) 

---------------------------------------------------------

Cumulative Attendance w Grace Period = 
VAR _date = MAX( Attendance[date] )
VAR _student = MAX( Attendance[studentID])
VAR _gracedate = [28 Day Grace]
VAR _calc = SUMX( FILTER( ALL( Attendance ) , Attendance[date] <= _date && Attendance[date] > _gracedate && Attendance[studentID] = _student ) , Attendance[isPresent] ) 

RETURN
IF( [Cumulative School Days] <= 28 , _calc + [Cumulative School Days] , _calc +28 )

---------------------------------------------------------

Attendance Rate w Grace Period = DIVIDE( [Cumulative Attendance w Grace Period] , [Cumulative School Days] , BLANK() ) 

And the output is stuff like this:

I'm sure there's a much better way you want to organize or write your measures. This was just my play file, but I think it gets you pointed in the direction you articulated. Let me know if you have any questions.

[CoreyP]

Oh, I forgot to mention that I didn't spend time on fixing the DAX so they total correctly in matrices or card visuals and whatnot, so you'll need to do that if you're wanting to show totals or averages, etc.