A cohort analysis (matrix visual)

[pg1980]

Hi!

I have to be able to make a cohort 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”.

For example, for the row 2023-06 (from “year-month New”)  I have a total sum of 199 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) ==> 199 – 165 = 64

Table:

"Product"	"Quantity"	"year-month New"	"year-month Out"
A	225	2023-05	2023-11
A	314	2023-05	2023-10
A	8	2023-05	2023-08
A	14	2023-05	2023-08
A	45	2023-05	2023-09
A	141	2023-05	2023-09
A	4	2023-05	2023-10
A	4	2023-05	2023-05
A	13	2023-05	2023-05
A	4	2023-05	2023-06
A	3	2023-05	2023-06
A	1	2023-05	2023-06
A	1	2023-05	2023-06
A	2	2023-05	2023-07
A	82	2023-06	2023-07
A	53	2023-06	2023-07
A	10	2023-06	2023-08
A	6	2023-06	2023-08
A	42	2023-06	2023-09
A	2	2023-06	2023-09
A	2	2023-06	2023-10
A	1	2023-06	2023-10
A	1	2023-06	2023-11

Total quantity (using year-month New):

	TOTAL
2023-05	779
2023-06	199

Result:

	1	2	3	4	5	6	7	8	9	10	11
2023-05	762	753	751	729	543	225	0
2023-06	199	64	48	4	1	0

[pg1980]

Hi @tamerj1! Thanks for your help!

It´s working!! And if I want to customize the numer of month using like this:

Months Difference= GENERATESERIES(1, 12, 1)

[tamerj1]

@pg1980 

Yes you may do that. In this example 0 will be populated over all months greater than 7

[pg1980]

Hi @tamerj1,

I found a problem in the first measure you did, and also applies in the second measure.

If you don´t have a date in "year-month Out", its adding this amount in the first month. 

For example: 

"Product"	"Quantity"	"year-month New"	"year-month Out"
A	2	2023-05
A	4	2023-05
A	3	2023-05
A	1	2023-05
A	2	2023-05

If you add this rows,  you have a total for "2023-05": 791

	1	2	3
2023-05	762	753	751

In the first month it has to be: 774 (791-(4+13)) , not 762 (791-(4+13+2+4+3+1+2))

This should be de right output:

	1	2	3	4	5	6	7	8	9	10
2023-05	774	765	763	741	555	237	12	12	12	12

[pg1980]

Hi @tamerj1 ,

it´s ok!

And if i have to divide like this:

	1	2	3	4	5	6	7	8	9	10	11
2023-05	(762/779)	(753/762)	(751/753)	(729/751)	(543/729)	(225/543)	(0/225)
2023-06	(199/199)	(64/199)	(48/64)	(4/48)	(1/4)	(0/1)

In column n° 1: you have to divide the first value from the total --> 762 / 779= 0,97

In column n° 2: you have to divide the second value with the first value -> 753/762 = 0.98

in column n°3 : you have to divide the third value with the second value -> 751/753

in column n°4 : you have to divide the forth value with the thrd value ->  729/751

	1	2	3	4	5	6	7	8	9	10	11
2023-05	0,978	0,988	0,997	0,970	0,744	0,414	0
2023-06	1	0,321	0,75	0,08	0,25	0

[tamerj1 · Accepted solution]

@pg1980 
See attached updated sample file

Measure = 
VAR MonthDiff = MAX ( 'Months Difference'[Months] )
VAR T1 = 
    FILTER ( 
        'Table',
        DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH ) 
            >= MonthDiff - 1
    )
VAR T2 = 
    FILTER ( 
        T1,
        DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH ) 
            >= MonthDiff
    )
RETURN
    DIVIDE (
        SUMX ( T2, 'Table'[Quantity] ),
        SUMX ( T1, 'Table'[Quantity] )
    ) + 0

[tamerj1]

Hi @pg1980 
Please refer to attached sample filw with the proposed solution

Measure = 
SUMX (
    FILTER ( 
        'Table',
        DATEDIFF ( 'Table'[year-month New], 'Table'[year-month Out], MONTH ) 
            >= MAX ( 'Months Difference'[Months] )
    ),
    'Table'[Quantity]
) + 0