Daily to Day changes in matrix

[sambgv]

my ultimate goal is to display daily resource changes in a matrix. let me explain: 

Suppose, Team A requested YESTERDAY the role Engineer for February 2025 for a project in quantity of 5. Today the same team requests 4 of the same role. 

i need to display the change in
1 matrix: Months as columns, teams as rows, and aggregated values for ALL roles in cells. 
2 matrix: is sort of a drill down of the 1 matrix with Months as columns, selected Team AND Roles as rows and values in cells. 

My fact table looks like something like this :

date_updated	month	team	role	value
13/01/2025	02/2025	Team A	Engineer	5
13/01/2025	02/2025	Team A	Architect	4
14/01/2025	02/2025	Team A	Engineer	3
14/01/2025	02/2025	Team A	Architect	1

date_updated  = when the table was updated
month = for what month is the role required 

team = team 
role = role 

value = how much of a role is needed

And of course, i have a standard Date Table as a dim. 

Again, i would like compare the latest updated version of the table with the previous date (s), going back at least a week. so for eg. If the latest update was today, i'd like to compare it with yesterday, 2 days ago, 3 and so on, at least 1 week back. and display changes in the matrix .

I would appriciate any help as to how structure the model and DAX code to use in this scenario. 

[Anonymous]

Hi @sambgv ,

The matrix itself comes with a drill-down feature, and I think a single matrix would be sufficient for your needs. If you need the matrix to be on a different page, you can implement matrix 1 to matrix 2 filtering like this.

Matrix1

Matrix2

Your second requirement is not very clear to me, please provide more information such as expected output.

Best regards,

Mengmeng Li

[sambgv]

@Anonymous thanks for the reply !

1). how did you tackle the updated_date? you see, the table has been updated with those values in the matrix, so they will be different today. and i need to display that  on the matrix. i am aware of the drill-down option. I'm just struggling to capture changes in the matrix. do you know what i mean? 

so for eg take team A. 

updated_date : 14.01.2025 

values for 02/2025: 13

values for 03/2025: 5
values for 04/2025: 0 

updated_date: 15.01.2025

values for 02/2025: 10

values for 03/2025: 7
values for 04/2025: 4

in this scenario, I need to see what happened between 14.01 and 15.01 so:

values for 02/2025: -3 (decrease of )

values for 03/2025: 2 (increase of 2)
values for 04/2025: 4 (increase of 4)

2) the second requirnment is: in whatever team a change has occured, for what roles exactly has this changed occured? but this one is less important than the 1st requirnment, so let's focus on the first one. 


 

[Anonymous]

Hi @sambgv ,

Based on your description, would you like to show both each month's data when the user has selected Team and Role, as well as show the growth of the current month compared to the previous one?

If I understand correctly, you can add such a measure to the matrix.

increase = 
VAR _previous =CALCULATE(SUM('Table'[value]),ALL('Table'),MONTH('Table'[Month])=MONTH(MAX('Table'[Month]))-1,'Table'[team]=MAX('Table'[team]),'Table'[role]=MAX('Table'[role]))
VAR _current = SUM('Table'[value])
RETURN _current-_previous

Best regards,

Mengmeng Li

[sambgv]

thanks for your efforts. almost there!

my example: 

VAR Recent =

CALCULATE(

    SUM(table [value]),

    LASTDATE(table[date_updated])

)

VAR Previous 

CALCULATE(

    SUM(table [value]),

    LASTDATE(table[date_updated]) - 1

)

RETURN 
Recent - previous. 

date_updated column is the key here. it's the date from a database. it's when the data gets refreshed. and it's refreshed everyday. so i need the latest update - previous updates to see the resource changes for all months, teams, and roles. 

[Anonymous]

Hi @sambgv ,

What do your current results look like? Can you provide screenshots and a pbix file(without private data)?

Best regards,

Mengmeng Li