Forum Discussion

pg1980's avatar
pg1980
Helper 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 of each month “year-month Out”.

 

Table:

 

ProductQuantityyear-month Newyear-month Out
AA  52023-06 
AA  132023-05 
AA  12023-07 
AA  12023-062023-11
AA  12023-062023-10
AA  22023-062023-10
AA  22023-062023-09
AA  422023-062023-09
AA  62023-062023-08
AA  102023-062023-08
AA  532023-062023-07
AA  822023-062023-07
AA  22023-052023-07
AA  12023-052023-06
AA  12023-052023-06
AA  32023-052023-06
AA  42023-052023-06
AA  132023-052023-05
AA  42023-052023-05
AA  42023-052023-10
AA  1412023-052023-09
AA  452023-052023-09
AA  142023-052023-08
AA  82023-052023-08
AA  3142023-052023-10
AA  2252023-052023-11

 

For example, for the row 2023-06 (from “year-month New”)  I have a total sum of 204 and this is being substracted per month for the following month . This is the total result minus the quantity of 2023-07 (from “year-month Out”: 82+53) ==> 204 – 135 = 69.
The last value ("5") has to repeat until de last month (“year-month Out")

 

Total quantity (using year-month New):

 

 TOTAL
2023-05  792
2023-06  204
2023-07  1

 

Result:

 

   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

 

  • 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.

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
    Helper 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
      Community 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
        Helper 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