Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Tracking status changes by date

Hi,  I'm working with student IDs and their statuses which change over time, with the date of the change given, where each student can have multiple rows of changes, as follows:

Student IdStatusDate of Status change
Student1A01/02/2020
Student1B13/02/2020
Student1C15/02/2020
Student2A05/02/2020
Student2B12/02/2020
Student2C14/02/2020
Student3A02/02/2020
Student3B03/02/2020

 

I'd like to be able to count at each date how many students are on each status, so when they change status the total in one column will reduce by 1 and in the next will increase by 1, as the following example:

 

 Count of Status
DateABC
01/02/2020100
02/02/2020200
03/02/2020110
04/02/2020110
05/02/2020210
06/02/2020210
07/02/2020210
08/02/2020210
09/02/2020210
10/02/2020210
11/02/2020210
12/02/2020120
13/02/2020030
14/02/2020021
15/02/2020012

 

I would then use this in a line chart (date linked to existing date table) in order to show changes over time.

 

Any advice on how to do this would be much appreciated, thanks!

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

  • AlB's avatar
    AlB
    5 years ago

    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 

     

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the quick response AlB - I've added the measure in but am getting an error message that 'A table of multiple values was supplied where a single value was expected'.

       

      Do I need to use FIRSTNONBLANK at all?  There are null values in both Status and Date Change columns.

       

      Any help you can give?  Thanks

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you AlB, this works perfectly for my line chart once I cleaned the data up, and I've used it in a matrix too.  Thanks for the solution!