Square Matrix

[pg1980]

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:

Product	Quantity	year-month New	year-month Out
AA	5	2023-06
AA	13	2023-05
AA	1	2023-07
AA	1	2023-06	2023-11
AA	1	2023-06	2023-10
AA	2	2023-06	2023-10
AA	2	2023-06	2023-09
AA	42	2023-06	2023-09
AA	6	2023-06	2023-08
AA	10	2023-06	2023-08
AA	53	2023-06	2023-07
AA	82	2023-06	2023-07
AA	2	2023-05	2023-07
AA	1	2023-05	2023-06
AA	1	2023-05	2023-06
AA	3	2023-05	2023-06
AA	4	2023-05	2023-06
AA	13	2023-05	2023-05
AA	4	2023-05	2023-05
AA	4	2023-05	2023-10
AA	141	2023-05	2023-09
AA	45	2023-05	2023-09
AA	14	2023-05	2023-08
AA	8	2023-05	2023-08
AA	314	2023-05	2023-10
AA	225	2023-05	2023-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

[pg1980]

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]

@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]

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]

@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 )

[pg1980]

 And if i have to use also the "total" row with the same logic?

	0	1	2	3	4	5	6	7	8	9
2023-05	792	775	766	764	742	556	238	13	13	13
2023-06	204	204	69	53	9	6	5	5	5	5

Total

(792 + 204)

(775 + 204) / (792 + 204)

(766 + 69) / (774 + 204)

(764 + 53) / (766 + 69)

(742 + 9) / (764 + 53)

(556 + 6) / (742 + 9)

(238 + 5) / (556 + 6)

(13 + 5) / (238 + 5)

(13 + 5) / (13 + 5)

(13 + 5) / (13 + 5)

(13 + 5) / (13 + 5)

[tamerj1 · Accepted solution]

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.