Forum Discussion

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

A cohort analysis (matrix visual)

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
pg1980's avatar
pg1980
Icon for Helper II rankHelper II
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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