Forum Discussion

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

Sumarize a Cohort Measure

Hi! I have this measure to do a cohort analysis, but i need to sumarize in totals. One option is to use the variable "Monthdiff", changing the value from 1 to 12 and sumarize at the end.   Original...
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi pg1980 

    you may try

    MeasureCohort =
    SUMX (
    'table2',
    VAR MonthDiff = 'table2'[Months]
    VAR MaxOutDate =
    CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )
    RETURN
    SUMX (
    FILTER (
    'Table1',
    DATEDIFF (
    'Table1'[year-month New],
    COALESCE ( 'Table1'[year-month Out], MaxOutDate ),
    MONTH
    ) >= MonthDiff
    ),
    'Table1'[Quantity]
    ) + 0
    )

     

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

I created one measure for each value but it is not usefull. for example:

Measure Cohort 1 =
VAR MonthDiff = 1
VAR MaxOutDate =
CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )
RETURN
SUMX (
FILTER (
'Table1',
DATEDIFF (
'Table1'[year-month New],
COALESCE ( 'Table1'[year-month Out], MaxOutDate ),
MONTH
) >= MonthDiff
),
'Table1'[Quantity]
) + 0

Measure Cohort Total= Measure Cohort 1 + Measure Cohort 2 + Measure Cohort 3 + Measure Cohort 4 + Measure Cohort 5 .....