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

pg1980 

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

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

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