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
    )

     

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