Tracking status changes by date

Hi Anonymous

Create a date table to be used in the axis (no relationship to the fact table) and create this measure. You can use it in a matrix visual or a chart.  See it all at work in the attached file.

Measure = 
VAR currentDate_ = MAX ( DateT[Date] )
VAR currentStatus_ = DISTINCT ( Table1[Status] )
VAR auxT_ =
    CALCULATETABLE (
        ADDCOLUMNS (
            ALL ( Table1[Student Id] ),
            "@res",
                VAR lastDate_ =
                    CALCULATE (
                        MAX ( Table1[Date of Status change] ),
                        Table1[Date of Status change] <= currentDate_
                    )
                RETURN
                    CALCULATE (
                        DISTINCT ( Table1[Status] ),
                        Table1[Date of Status change] = lastDate_
                    )
        ),
        ALL ( Table1[Status] )
    )
RETURN
    COUNTROWS ( FILTER ( auxT_, [@res] IN currentStatus_ ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Anonymous 

Most likely it has to do with the DISTINCT ( ) in this bit:

  CALCULATE (
                        DISTINCT ( Table1[Status] ),
                        Table1[Date of Status change] = lastDate_
                    )

It probably means there is more than one Status for the latest data and that student. Check if there are cases like that and see how you want to solve them. Perhaps with MAX, or perhaps you need to clean the data first

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers