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 Measure:
Measure Cohort =
VAR MonthDiff =
MAX ( '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
Table1:
| Product | Quantity | year-month New | year-month Out |
| AA | 5 | 2023-06 | |
| AA | 13 | 2023-05 | |
| AA | 1 | 2023-07 | |
| AA | 1 | 2023-06 | 2023-11 |
| AA | 1 | 2023-06 | 2023-10 |
| AA | 2 | 2023-06 | 2023-10 |
| AA | 2 | 2023-06 | 2023-09 |
| AA | 42 | 2023-06 | 2023-09 |
| AA | 6 | 2023-06 | 2023-08 |
| AA | 10 | 2023-06 | 2023-08 |
| AA | 53 | 2023-06 | 2023-07 |
| AA | 82 | 2023-06 | 2023-07 |
| AA | 2 | 2023-05 | 2023-07 |
| AA | 1 | 2023-05 | 2023-06 |
| AA | 1 | 2023-05 | 2023-06 |
| AA | 3 | 2023-05 | 2023-06 |
| AA | 4 | 2023-05 | 2023-06 |
| AA | 13 | 2023-05 | 2023-05 |
| AA | 4 | 2023-05 | 2023-05 |
| AA | 4 | 2023-05 | 2023-10 |
| AA | 141 | 2023-05 | 2023-09 |
| AA | 45 | 2023-05 | 2023-09 |
| AA | 14 | 2023-05 | 2023-08 |
| AA | 8 | 2023-05 | 2023-08 |
| AA | 314 | 2023-05 | 2023-10 |
| AA | 225 | 2023-05 | 2023-11 |
table2=
GENERATESERIES (0,12,1)
Expected Result:
| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | total |
| 2023-05 | 775 | 766 | 764 | 742 | 556 | 238 | 13 | 13 | 13 | 13 | 13 | 13 | 3919 |
| 2023-06 | 204 | 69 | 53 | 9 | 6 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 376 |
| 2023-07 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 12 |
| | | | | | | | | | | | | | 4307 |
| | Row total |
| 2023-05 | 3919 |
| 2023-06 | 376 |
| 2023-07 | 12 |
| TOTAL | 4307 |
