Measure to return the most recent known value

[ttseng]

Hi,

I'm struggling to get a measure that returns the most recent known value. 

I have a dataset of Schools that take enrollment. Sometimes the enrollment is taken and sometimes it is not. I want to be able to return the the most recent known value of students enrolled for each school. If enrollment was never taken then return a null. 

Here's the sample of the raw data. 

School_ID	Date	Take_Enrollment	Students
1015	3/19/2020	No	null
1016	3/11/2020	Yes	288
1017	2/12/2020	No	null
1017	3/11/2020	Yes	423
1017	3/17/2020	No	null
1018	3/18/2020	Yes	410
1019	3/17/2020	No	null
1020	3/11/2020	Yes	103
1021	2/12/2020	No	null
1021	4/14/2020	Yes	262
1022	2/14/2020	No	null
1022	4/14/2020	Yes	241
1023	2/12/2020	No	null
1023	4/14/2020	No	null

Here is what I'd like to be the final output:

School_ID	Students
1015	null
1016	288
1017	423
1018	410
1019	null
1020	103
1021	262
1022	241
1023	null

note:

What is causing me trouble is when I encounter something like school_id 1017 where the latest value is null but I actually want it to return 423 when enrollment was last taken.

Thanks!

[edhans · Accepted solution]

This will work.

Enrollment =
VAR LatestDate =
    MAX( 'Table'[Date] )
VAR EnrollmentTakenTable =
    FILTER(
        'Table',
        'Table'[Take_Enrollment] = "Yes"
    )
VAR EnrollmentTaken =
    COUNTROWS( EnrollmentTakenTable ) >= 1
VAR LatestEnrollmentTakenDate =
    MAXX(
        EnrollmentTakenTable,
        'Table'[Date]
    )
VAR LatestValue =
    IF(
        EnrollmentTaken
            = TRUE(),
        MAXX(
            EnrollmentTakenTable,
            'Table'[Students]
        ),
        MAXX(
            FILTER(
                'Table',
                'Table'[Date] = LatestDate
            ),
            'Table'[Students]
        )
    )
RETURN
    LatestValue

It returns this table.

In order to get the blanks to show up though you need to select the School ID in the visual fields and tell it to show items with no data, otherwise, 1015, 1019, and 1023 will not show up.

Edit: @Tahreem24's solution is much simpler, but I don't think it will work if your database has multiple "Yes" enrollments taken for the same school ID. I assumed you would actually have school 1017, for example, in there many times and you only wanted the latest enrollment taken if any ever had been, or null if it never had been. If your records are not going to work like that but will be representative of your sample data, you should definitely use @Tahreem24's solution.

[ttseng]

Thanks @edhans for the quick work. The solution worked perfectly!

[edhans]

Great @ttseng - glad it worked for you.

[ttseng]

Hi @edhans -

Thanks again for the solution. I have a new use case based on this need for most recent known value and am wodnering if you could help.

I have targets and actuals and targets are monthly based on number of students registered multiplied by number of school days that month. I have a table with the schools days per month and can query that no problem. However, I'm having trouble trying to get the targets per month per school based on the dynamic student enrollment as some months they are entered and other not. 

Here's the same dataset.

School_ID	Date	Take_Enrollment	Students
1015	3/19/2020	No	null
1016	3/11/2020	Yes	288
1017	2/12/2020	No	null
1017	3/11/2020	Yes	423
1017	3/17/2020	No	null
1018	3/18/2020	Yes	410
1019	3/17/2020	No	null
1020	3/11/2020	Yes	103
1021	2/12/2020	No	null
1021	4/14/2020	Yes	262
1022	2/14/2020	No	null
1022	4/14/2020	Yes	241
1023	2/12/2020	No	null
1023	4/14/2020	No	null

I would like to return a table with unique schools and enrollment for each month based on the most recent known value of enrollment. This is what i'm hoping to achieve as a seperate table to track my targets.

School_ID	February	March	April
1015	null	null	null
1016	null	288	288
1017	null	323	423
1018	null	410	410
1019	null	null	null
1020	null	103	103
1021	null	null	262
1022	null	null	241
1023	null	null	null

I would like each month to have the most recent known value of student enrollment unless data was entered that month. 

for example: 

School 1016 only has data from March and it will use that value and fill April, June, July and so forth until new enrollment is entered that would take its place for future months

School 1017 has data for both March and April and will reutrn the corresponding numbers. If enrollment data is not entered in June it would use April's enrollment.

The end result would then take enrollment for each month that's selected in a filter and multiply by schools days for each respective months and added together to get targets for that date range. 

Is this this right approach in thinking about these targets? If so, how would one go about achieving this?

This data can be bound through December 2020 if that's helpful.

Thanks again!

[Tahreem24]

Try something like below:

measure= If(Table[TakeEnrollment]="Yes", Sum(Table[Student]), Sum(Table[Student]))