Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
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 |
Solved! Go to Solution.
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
)
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
)
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 .....
User | Count |
---|---|
66 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |