Forum Discussion

pg1980's avatar
pg1980
Helper 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 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"
A2252023-052023-11
A3142023-052023-10
A82023-052023-08
A142023-052023-08
A452023-052023-09
A1412023-052023-09
A42023-052023-10
A42023-052023-05
A132023-052023-05
A42023-052023-06
A32023-052023-06
A12023-052023-06
A12023-052023-06
A22023-052023-07
A822023-062023-07
A532023-062023-07
A102023-062023-08
A62023-062023-08
A422023-062023-09
A22023-062023-09
A22023-062023-10
A12023-062023-10
A12023-062023-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     

 

 

  • 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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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
  • pg1980's avatar
    pg1980
    Helper 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
      Community 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
  • pg1980's avatar
    pg1980
    Helper 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
      Community 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
        Helper 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