Forum Discussion

sambgv's avatar
sambgv
Icon for Helper I rankHelper I
1 year ago

Daily to Day changes in matrix

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_updatedmonth teamrolevalue
13/01/202502/2025Team AEngineer5
13/01/202502/2025Team AArchitect4
14/01/202502/2025Team AEngineer3
14/01/202502/2025Team AArchitect1


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. 

Need Help
Tips and Tricks

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      sambgv
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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