Calculate number of active students - showing it back in time

[Anonymous]

Hi there

I have hit a wall with making a calculation.

The aim of the calculation is to calculate the amount of people who we consider as active - which is determined by several datediff calculations, as well as some other conditions.

The measure works well for showing me how many active students we have now, but I cannot figure out a way to get it to accurately calculate how many active students there were on some date in the past. I need to be able to show on a line graph how the amount of active students has developed since November 2021.

I typed up a shortened version of the calculation in english:

Active Students =
CALCULATE(
DISTINCTCOUNT('Demand'[CitizenId]),
ISBLANK('Demand'[Exam Passed Date]),
not(isblank('Demand'[Doctor Certification Date])),
not(ISBLANK('Demand'[Application Accepted Date])),
'Demand'[Application Paid?] = TRUE(),
Category[Category] = "B",
'Demand'[Doctor Certificate - Today] < 365, //datediff column - Doctor Certification Date to today - the rest of the columns referred to below are also datediff columns.
'Demand'[Last Exam Date - today] > 0 && 'Demand'[Last Exam Date - Today] < 92 || 'Demand'[Application Accepted Date - today] < 183,
'Demand'[First Exam Attempt Date - Last Exam Attempt Date] > 0 && 'Demand'[First Exam Attempt Date - Last Exam Attempt Date] < 92 || 'Demand'[Application Accepted Date - today] < 183,
'Demand'[Application Accepted Date - First Exam Attempt Date] > 0 && 'Demand'[Application Accepted Date - First Exam Attempt Date] < 183 || 'Demand'[Application Accepted Date - today] < 183
)

Like I said, it works perfectly if i want to see how many active students there are now, but not if I want to see how many there have been in the recent months - it would be nice to see this development on a line graph.

I have a relationship set up between a date table and the various date columns. The relationships to the three dates mentioned in the measure are inactive.

[tamerj1]

Hi @Anonymous 
Please refer to this sample file with the solution https://www.dropbox.com/t/qrX8DgZMMqRlgU7N

I had a hard time trying to understand you filter conditions. However, I believe you can revise according to your requirement. Also I could not apply some conditions related to other tables or other columns which are not available in the sample data. The measure is long but simple. Columns aggregate at ID level are created virtually to enable simple application of the conditions according to your requirement.

Active Students = 
VAR SelectedDate =
    MAX ( 'Date'[Date] )
VAR IDTable1 =
    ADDCOLUMNS ( 
        VALUES ( Demand[ID] ),
        "@Last Update", MAX ( Demand[Event Date] ),
        "@Doctor Certificate", CALCULATE ( MAX ( Demand[Event Date] ), Demand[Event Type no.] = 16 ),
        "@Exam Passed", CALCULATE ( MAX ( Demand[Event Date] ), Demand[Event Type no.] = 10 ),
        "@Last Exam", CALCULATE ( MAX ( Demand[Event Date] ), Demand[Event Type no.] IN { 7, 10 } ),
        "@First Exam", CALCULATE ( MIN ( Demand[Event Date] ), Demand[Event Type no.] IN { 7, 10 } ),
        "@Application Acceptance", CALCULATE ( MAX ( Demand[Event Date] ), Demand[Event Type no.] = 1 )
    )
VAR IDTable2 =
    ADDCOLUMNS (
        IDTable1,
        "@Doctor Certificate Days", DATEDIFF ( [@Doctor Certificate], SelectedDate, DAY ),
        "@Last Exam Days", DATEDIFF ( [@Last Exam], SelectedDate, DAY ),
        "@First Exam Days", DATEDIFF ( [@First Exam], SelectedDate, DAY ),
        "@First-Last Exam Days", DATEDIFF ( [@First Exam], [@Last Exam], DAY ),
        "@Application Acceptance Days", DATEDIFF ( [@Application Acceptance], SelectedDate, DAY ),
        "@Application-First Days", DATEDIFF ( [@First Exam], [@Application Acceptance], DAY )
    )
VAR Result =
    SUMX (
        IDTable2,
        IF (
            ISBLANK ( MAX ( [@Exam Passed] ) )
            && NOT ISBLANK ( [@Doctor Certificate] )
                && NOT ISBLANK ( [@Application Acceptance] )
                    && [@Doctor Certificate Days] < 365
                        && AND ( [@Last Exam Days] > 0, OR ( [@Last Exam Days] < 92, [@Application Acceptance Days] < 183 ) )
                            && AND ( [@First-Last Exam Days] > 0, OR ( [@First-Last Exam Days] < 92, [@Application Acceptance Days] < 183 ) )
                                && AND ( [@Application-First Days] > 0, OR ( [@Application-First Days] < 183, [@Application Acceptance Days] < 183 ) ),
        
        1,
        0
        )
    )
RETURN
    Result

[Anonymous]

Hi @tamerj1 

Thank you so much, this measurement gives roughly what I expect the outcome to be.

However, the next step is to show what it has been over time on a line graph.

If I plot the date from the date table on the x-axis and add the measurement, it comes out like this:

If we take the example of the student Sarah Adams, who becomes active on the 01.12.2021 and is therefore denoted on the line graph with a 1. However she is still active, so the graph should remain at 1 until the next student becomes active in March 2022, when it would then increase to two. 

The line should also fall when students become inactive (they pass their exam, or go longer than the datediff calculations state).

I hope that makes sense.

Is there a way to do this?

[tamerj1 · Accepted solution]

@Anonymous 
That is becasue the measure was designed for a card visual. No issues you can create another measure as a running total of the first one as follows:

Active Students RT = 
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Active Students],
        'Date'[Date] <= CurrentDate
    )

And here is the updated file https://www.dropbox.com/t/T3XWvdsbjheMehVU

[Anonymous]

That is it! Thank you so much for your help 🙂

[tamerj1]

@Anonymous 

The issue is not with code. The issue is with the structure of the data. 
Your data is based on columns which have fixed values. One example out of many are the columns that count the days based on TODAY() date. I guess these are calculated columns. As you calculated columns do not interact with the filter context. For instance you cannot replce TODAY() with SELECTEDVALUE( DateColumn), it's not going to work. 
you got to restructure your data so that you can rely on only one date column with can be filtered based on the selected date range. This will require comprehensive data unpivoting (less columns and many more rows). In other words, every transaction should have a row and this row contains the date of the tansaction, the tranaction identifier ("registration", "application accepted" or "complete test", etc.), student ID and name and other relevant information such as faculty if applicable. 
this can be achieved using excel, sql, dax or power query upon you preference. 

[Anonymous]

Thanks for your response.

I believe the date is actually structured the way you suggest it should be.

However I do not know of a way to write the required measure when the data is structured as it is.

For example, instead of using the calculated column 'Exam Passed Date' I would have to use the single date column, but only where the event = "exam passed".

Active Students =
CALCULATE(
DISTINCTCOUNT('Demand'[CitizenId]),
ISBLANK('Demand'[Date]) // after this point I would presumably need to write "event = Exam Passed" and continue this for all of the other filter conditions

How would you recommend that I write this?

[tamerj1]

Yes somthing like that. Can you please share done sample data?

[Anonymous]

@tamerj1 I have attempted something like this:

TEST MEASURE =
var first_exam = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),
'Demand'[Event Type] = 4 || 'Demand'[Event Type] = 5 || 'Demand'[Event Type] = 9 || 'Demand'[Event Type] = 12 || 'Demand'[Event Type] = 21 || 'Demand'[Event Type] = 22 || 'Demand'[Event Type] = 23 || 'Demand'[Event Type] = 24 || 'Demand'[Event Type] = 25 || 'Demand'[Event Type] = 26 || 'Demand'[Event Type] = 27 || 'Demand'[Event Type] = 28)

var latest_exam = CALCULATE(MAX('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),
'Demand'[Event Type] = 4 || 'Demand'[Event Type] = 5 || 'Demand'[Event Type] = 9 || 'Demand'[Event Type] = 12 || 'Demand'[Event Type] = 21 || 'Demand'[Event Type] = 22 || 'Demand'[Event Type] = 23 || 'Demand'[Event Type] = 24 || 'Demand'[Event Type] = 25 || 'Demand'[Event Type] = 26 || 'Demand'[Event Type] = 27 || 'Demand'[Event Type] = 28)

var Accepted_date = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]), 'Demand'[Event Type] = 1)

var Doctor_date = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]), 'Demand'[Event Type] = 16)

var current_date = max('Date'[Date])

var exam_passed = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),'Demand'[Event Type] = 10)

var theory_passed = CALCULATE(MIN('Demand'[CreatedDate]), ALL('Demand'),'Demand'[CitizenId] = MAX('Demand'[CitizenId]),'Demand'[Event Type] = 4)

return

calculate(
distinctcount( 'Demand'[citizenid] ),
Kategori[Kategori] = "B",
'Demand'[Application_paid?] = TRUE(),
ISBLANK(exam_passed),
not(isblank(Doctor_date)),
not(ISBLANK(Accepted_date)),
datediff(first_exam, latest_exam,DAY) > 0 && datediff(first_exam, latest_exam,DAY) < 92 || datediff(Accepted_date, current_date,DAY) < 183,
datediff(Accepted_date, first_exam, DAY) > 0 && datediff(Accepted_date, first_exam, DAY) < 183 || datediff(Accepted_date, current_date,DAY) < 183,
datediff(Doctor_date, current_date, DAY) < 365,
datediff(latest_exam, current_date,DAY) > 0 && datediff(latest_exam, current_date,DAY) < 92 || datediff(Accepted_date, current_date,DAY) < 183)

Using variables to calculate the required dates that I need out from the one date column (Created Date). 

The problem is that I get the error "A True/False expression does not provide one column. Every True/False expression that is used as a table filter expression must refer to exactly one column.

Is there any way around this error?

[Anonymous]

ID	Name	Event Type	Event Type no.	Event Date
1000001	John Smith	Doctor Certification	16	01-12-2021
1000001	John Smith	Exam Passed	10	01-03-2022
1000001	John Smith	Exam Failed	7	31-01-2022
1000001	John Smith	Did not show up to exam	6	10-01-2022
1000001	John Smith	Application Accepted	1	30-11-2021
1000005	Sarah Adams	Exam Failed	7	18-01-2022
1000005	Sarah Adams	Did not show up to exam	6	30-01-2022
1000005	Sarah Adams	Application Accepted	1	01-12-2021
1000005	Sarah Adams	Doctor Certification	16	01-12-2021
1000005	Sarah Adams	Exam Failed	7	20-02-2022
1000010	David Jones	Application Accepted	1	01-06-2021
1000010	David Jones	Doctor Certification	16	01-06-2021
1000020	Kirsty Roberts	Application Accepted	1	01-03-2022
1000020	Kirsty Roberts	Doctor Certification	16	01-03-2022
1000022	James Bond	Application Accepted	1	01-02-2022
1000022	James Bond	Doctor Certification	16	02-02-2022
1000022	James Bond	Exam Failed	7	15-03-2022

Unfortunately I cannot share the data, but here is a very brief idea of what it looks like.

In this scenario David Jones and John Smith should not be considered as active, John Smith has passed the exam, and David Jones has not done anything in the last 183 days. So there should be 3 active students.

The only way I can figure out how to write this almost involves putting a filter on a filter if that makes sense? 

E.g. Event Date < 183 only where Event Type no = 1.

Is that possible?