Tracking changes month on month with multiple results!!

[forgetmenot]

Hi all, 

I wonder if someone can help. 

Client wants to track the month on month changes between their projects categories. 
All prjects are assigned a colour category.
The issue im facing is that these projects can be labeled a number of colours at the same time. Client wants to be able to look at the changes happening to these colours per project per month. 


I added custom columns to dataset to be able to count up the number of reds, green. ambers and blues. 

Amber?	Blue?	Red?	Green?	Month	Project Workstream
amber	blue	red	green	01/01/2024	Stream1
exclude	blue	red	green	01/02/2024	Stream2
amber	exclude	red	green	01/06/2024	Stream3
amber	blue	exclude	green	01/01/2024	Stream4
amber	blue	red	Exclude	01/05/2024	Stream1
exclude	blue	red	green	01/06/2024	Stream2
amber	exclude	red	green	01/09/2024	Stream3
amber	blue	exclude	green	01/02/2024	Stream4
amber	exclude	red	green	01/02/2024	Stream3
amber	blue	exclude	green	01/06/2024	Stream4
amber	blue	red	Exclude	01/05/2024	Stream1
exclude	blue	red	green	01/06/2024	Stream1
amber	exclude	red	green	01/09/2024	Stream2

Can someone please advise how i can track this?

I have wrote dax measures to bring out concatenated result of latest month and concatenated results of previsu month but client woudl like it displayed in matrix. 

so far i have I have tried to create dax measure and even tried adding in columns :

CombinedColors =

    CALCULATE(

    TRIM(

        SUBSTITUTE(

            SUBSTITUTE(

                SUBSTITUTE(

                    SUBSTITUTE(

                        CONCATENATEX(

                            'table',

                            IF('table'] <> "exclude", 'table'[amber count] & ", ", "") &

                            IF('table'[Blue count 24-25] <> "exclude", 'table[Blue count 24-25] & ", ", "") &

                            IF('table_files'[count of red 24-25] <> "exclude", 'table'[count of red 24-25] & ", ", "") &

                            IF('table'[green 24-25 count] <> "exclude", 'table'[green 24-25 count] & ", ", ""),

                            ", "

                        ),

                        ", ,", ","),  // Remove double commas

                    ", ,", ","),  // Remove any remaining double commas

                ", ,", ","),  // Remove any remaining double commas

            ", ,", ","  // Remove any remaining double commas

        )

    ),

    ALLEXCEPT('table', 'table'[Month], 'table'[project])

)

PreviousMonthColorsCOL =

CALCULATE(

    FIRSTNONBLANK(

        'table'[CombinedColors],

        1

    ),

    DATEADD('table'[Month], -1, MONTH),

    ALLEXCEPT('table', 'STP_2024_25_files'[project])

)

I keep getting errors when i try creating measure to compare the two:

HasChanged =

IF(

    'table' CombinedColors] <> 'table'[PreviousMonthColorsCOL],

    "Changed",

    "Not Changed"

)

Can someone suggest better way or help me out please??

thank you in advance!

[Ashish_Mathur]

Hi,

I am not sure of your end result.  First and foremost, you should select the last 2 columns (in the Query Editor), rght click and select "Unpivot Other Columns"

[Ashish_Mathur]

Hi,

I am not sure of your end result.  First and foremost, you should select the last 2 columns (in the Query Editor), rght click and select "Unpivot Other Columns"

[NordicDrow]

Hello there!

I believe one of this visuals way of representing the data could be of use in your case?
Take into account im considering each "Stream" as a project, and modified the input a bit to have 3 or 4 months of data.

You can also have text (I choose to hide it) and of course it need a lot more work to look nicer, but any of this would help users track which "colours" were set for each project each month.

Tell me if something like this would work, or you need something else I might have misunderstood.

Just as a heads up, I will be more detailed if need be, I unpivoted the 4 initial color columns to an attribute-value pair, then created the matrixes above and set conditional fomatting to background and font color.

[forgetmenot]

Thank you, that's helpful.

After posting here, I tried a similar approach where I wrote individual measures and added them as columns to the matrix, then conditionally formatted them according to color.

The issue I'm facing is that the client wants to know exactly what has changed and when. While the matrix provides this information, they want to see at a glance if there has been a change, such as a color moving from amber to red, green to red, red to green, etc. Essentially, they want all possible outcomes mapped and to be notified of which change has taken place.

They have also asked if they could get a count of the types of changes in the month. I need a way to tell them what the previous color was and what it is now. Are you able to provide a measure that brings back the selected month's colors in a card visual and then shows what the previous color was? So far, I have only been able to bring out the latest colors and one month prior