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! 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'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