How to visualize most recent known value (measure)

[ttseng]

Hi, 

background:

I have a dataset that tracks school performance based on attendance that categorizes the schools into low, medium, or high performing. 

The dataset looks like this:

School_ID	Date	Performance	score
1013	June 2020	0.5	Medium
1013	July 2020	0.75	Medium
1013	August 2020	1	high
1122	June 2020	0	0
1122	July 2020	0.7	Medium
1122	August 2020	0.9	high
1135	June 2020	0.6	Medium
1135	July 2020	0.6	Medium
1135	August 2020	0.7	0

I have a measure that returns the most recent known score of the schools:

Recent_Score = 

VAR LatestDate = 
max(attendance[Date])
VAR LatestValue =
         MAXX(
            FILTER(
                attendance,
                attendance[Date] = LatestDate
            ),
            attendance[score]
        )
Return
    LatestValue

This works great when used across a date slicer so that we can always determine the most recent performance of that school based on the date (over time):

However, the issue I've run into and cannot seem to solve is that I would like to know: How many schools are performing in each category given the most recent known performance in that date period. I essentially want a stacked bar graph like below but instead of medium: 6, high:2, and 0: 1 it would show high: 2, Medium 1 based on the recent score in the matrix to the left.

I've attached a sample .pbix file here.

Help?

Thanks in advance.

[Ashish_Mathur]

Hi,

I'd like to help.  While you already have a Score column in your dataset, could you let me know the logic of assigning scores based on the performance?  Is it that 0.76 and above is high?  PLease clearly share the lower and upper value performance figure of each score.

[ttseng]

Hi @Ashish_Mathur ,

Thank you for your help.

The scores is supposed is based on a range:

"0" = "0",

<.5 = "low".

<.79 = "medium",

<= 1 = "High"

Thanks,

[DataInsights]

@ttseng, try this calculated column in the Values field well of the column chart:

Recent Score = 
VAR vSchool = attendance[School_ID]
VAR vSchoolTable =
    FILTER ( attendance, attendance[School_ID] = vSchool )
VAR vMaxDate =
    MAXX ( vSchoolTable, attendance[Date] )
VAR vTargetRow =
    FILTER ( vSchoolTable, attendance[Date] = vMaxDate )
VAR vResult =
    MAXX ( vTargetRow, attendance[score] )
RETURN
    IF ( attendance[Date] = vMaxDate, vResult, BLANK () )

[ttseng]

HI @DataInsights ,

Thanks for your work on this. I tested it and it works in returning the most recent score in the whole dataset. However, when I move the date slicer to anytime before the max date it returns blank. I would like for it to return the score for the max date defined by the date slicer. The example should return a column chart with 3 for medium since those are the scores in that date range. 

Thanks in advance for helping me.

[ttseng]

Hi @DataInsights,

Thank you! Your solution worked great!

[DataInsights · Accepted solution]

@ttseng, try this measure:

Recent Score =
VAR vMaxDate =
    LASTDATE ( ALLSELECTED ( attendance[Date] ) )
VAR vResult =
    CALCULATE ( COUNT ( attendance[score] ), vMaxDate )
RETURN
    vResult