topic Re: Square Matrix in DAX Commands and Tips

Square Matrix

pg1980 — Mon, 14 Aug 2023 20:37:33 GMT

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

Re: Square Matrix

tamerj1 — Mon, 14 Aug 2023 18:19:05 GMT

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

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.

Re: Square Matrix

pg1980 — Mon, 14 Aug 2023 20:03:46 GMT

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.

Re: Square Matrix

tamerj1 — Mon, 14 Aug 2023 20:31:11 GMT

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?

Re: Square Matrix

pg1980 — Mon, 14 Aug 2023 21:04:40 GMT

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:

2023-05:

(775/792)

(766/775)

(764/766)

(742/764)

(556/742)

(238/556)

(13/238)

(13/13)

2023-06:

(204/204)

(69/204)

(53/69)

(9/53)

(6/9)

(5/6)

(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

Re: Square Matrix

tamerj1 — Tue, 15 Aug 2023 02:29:18 GMT

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 )

Re: Square Matrix

pg1980 — Mon, 11 Sep 2023 22:42:22 GMT

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 +

(764

53)

(556 +

(742

(238 +

(556

(13

(238

(13