More than once a week

[HiltonS]

Hi Guys

I am currently working with attendance data of school learners. I would like to use a measure to calculate how many learners have attended more than once in a week across all weeks to date. I have a learner table, with unique learner number and date columns.

Hope you guys can help. 

Thanks

[OwenAuger]

Hi @HiltonS

This can definitely be done.

Just clarifying, do you want to calculate:

1. The number of learners who have attended more than once in at least one week among all weeks to date?
2. The number of learners who have attended more than once in every week to date?

Owen

[HiltonS]

Thanks for the reply. More than once every week.

[OwenAuger]

@HiltonS

Here is a prototype.

Suggested tables to make this work (Table: Columns):

1. Learner: Learner
2. Date: Date, Year-Week (or any sequential week column), End of Week flag (true/false, indicating whether a given date is the last day of a week)
3. Attendance: Learner, Date

Below is the measure I wrote to return the number of Learners who have attended more than once in every week so far.

To summarise what the measure is doing:

1. It works out which weeks have completely passed so far using a series of variables, and stores the result in the variable WeeksUpToMaxCompleteWeek. (If you only filter on whole weeks, then this whole calculation may be unnecessary.)
2. Then, in the section following RETURN, it takes the selected Learners and removes those who have had fewer than 2 attendances in any of these weeks.
3. Then it counts these Learners using COUNTROWS.

Number of Learners With More Than 1 attendance in all weeks to date = 
VAR ValuesLearners =
    VALUES ( Learner[Learner] )
VAR DatesUpToMaxDate =
    DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
VAR MaxEndOfWeekDate =
    CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[Last Day of Week], DatesUpToMaxDate )
VAR DatesUpToMaxCompleteWeek =
    DATESBETWEEN ( 'Date'[Date], BLANK (), MaxEndOfWeekDate )
VAR WeeksUpToMaxCompleteWeek =
    CALCULATETABLE ( VALUES ( 'Date'[Year-Week] ), DatesUpToMaxCompleteWeek )
RETURN
    CALCULATE (
        COUNTROWS (
            EXCEPT (
                ValuesLearners,
                SELECTCOLUMNS (
                    GENERATE (
                        WeeksUpToMaxCompleteWeek,
                        FILTER ( ValuesLearners, CALCULATE ( COUNTROWS ( Attendance ) ) < 2 )
                    ),
                    "Learner", Learner[Learner]
                )
            )
        ),
        DatesUpToMaxCompleteWeek
    )

Anyway, I guess this will need adapting to your model.

Regards,

Owen

[HiltonS]

See below my direct adaption to what you sent me.

My version

It doesnt seem to give any values.  What have I done wrong?

[OwenAuger]

@HiltonS

Thanks for posting your file - useful to have a look at your actual data.

Haven't had a chance to work out the solution, but there are two issues I can see:

1. My measure looks back at the entire Date table. In your case, you have weeks in the Date table before the first Attendance record, so all the Learners are deemed to have zero attendance in those weeks, meaning the measure returns (blank). I think we should adjust the measure to only look back as far as the earliest date in the Attendance table.
2. My measure as it stands performs quite poorly using your dataset (which is <400,000 rows), so will have to find a more efficient way of applying the same logic.

I will have a look into these points and get back to you. Let me know if you make any progress yourself.

Regards,

Owen 🙂