Forum Discussion

pg1980's avatar
pg1980
Icon for Helper II rankHelper II
3 years ago
Solved

Square Matrix

Hi everyone! I have to be able to make a analysis in a matrix visual. In this analysis I have to sum up the quantity values by “year-month New” and go substracting this number to the total quantity ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi pg1980 
    Please refer to attached sample file with the proposed solution

    Current Quantity = 
VAR MonthNew = SELECTEDVALUE ( 'Table'[year-month New] )
VAR MonthsShift = SELECTEDVALUE ( Months[Value] )
VAR MonthOut = EOMONTH ( MonthNew, MonthsShift - 2 ) + 1
VAR QtyNew = SUM ( 'Table'[Quantity] )
VAR QtyOut = 
    CALCULATE ( 
        SUM ( 'Table'[Quantity] ), 
        COALESCE ( 'Table'[year-month Out], DATE ( 2050, 1, 1 ) ) <= MonthOut
    )
RETURN
    QtyNew - QtyOut

    This is based on the assumption that year-month is date format (start of month). Also the expected results of July do not seem to be reasonable as the sample data data says 1 in in October and 1 out in October. However I have adjusted the sample data so that 1 in in July and 1 out in October.

pg1980's avatar
pg1980
Icon for Helper II rankHelper II
3 years ago

Yes, but it 2023-07 it has to repeat the vale 1 ultil the last month:

 

   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15
2023-05  775  766  764  742  556  238  13  13  13  13  13  13  13  13  13
2023-06  204  69  53  9  6  5  5  5  5  5  5  5  5  5  5
2023-07  1  1  1  1  1  1 1 1 1 1 1 1 1 1   1

 

In this case the value is "1" to have an example to  discribe the square matrix i need. The others year-mounth (2023-06 & 2023-05) are ok.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    pg1980 

    Yes I could see that in your expected result but how did you cone up with that reault? I could not see the logic behind it?

    • pg1980's avatar
      pg1980
      Icon for Helper II rankHelper II
      3 years ago

      Yes, you are right. I didn´t put a value for 2023-07. I have just corrected the table.

       

      And if i have to do like this:

       

           1    2    3    4    5    6    7    8    9    10    11    12    13    14    15
      2023-05:    (775/792)    (766/775)    (764/766)   (742/764)   (556/742)   (238/556)     (13/238)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)  (13/13)
      2023-06:    (204/204)  (69/204)  (53/69)  (9/53)  (6/9)  (5/6)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)  (5/5)

       

      In column n° 1: you have to divide the first value from the total --> 775 / 792= 0,97
      In column n° 2: you have to divide the second value with the first value ->  766/775= 0.98
      in column n°3 : you have to divide the third value with the second value ->   764/766
      in column n°4 : you have to divide the forth value with the thrd value ->   742/764

       

         1  2  3  4  5  6  7  8  9  10  11  12  13  14  15
      2023-05:  0,98  0,99  1  0,97  0,75  0,43  0,05  1  1  1  1  1  1  1  1
      2023-06:  1  0,34  0,77  0,17  0,67  0,83  1  1  1  1  1  1  1  1  1

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        pg1980 
        See attached updated sample file

        Current Quantity = 
VAR MonthNew = SELECTEDVALUE ( 'Table'[year-month New] )
VAR MonthsShift = SELECTEDVALUE ( Months[Value] )
VAR MonthOut = EOMONTH ( MonthNew, MonthsShift - 2 ) + 1
VAR QtyNew = SUM ( 'Table'[Quantity] )
VAR QtyOut = 
    CALCULATE ( 
        SUM ( 'Table'[Quantity] ), 
        COALESCE ( 'Table'[year-month Out], DATE ( 2050, 1, 1 ) ) <= MonthOut
    )
VAR QtyOutAfter = 
    CALCULATE ( 
        SUM ( 'Table'[Quantity] ), 
        COALESCE ( 'Table'[year-month Out], DATE ( 2050, 1, 1 ) ) < MonthOut 
    )
RETURN 
    DIVIDE ( QtyNew - QtyOut, QtyNew - QtyOutAfter )