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

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