topic Re: Sumarize a Cohort Measure in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787507#M147956 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="390705" data-lia-user-login="pg1980" class="lia-mention lia-mention-user">pg1980</a>&nbsp;</P> <P><SPAN>you may try</SPAN></P> <P>MeasureCohort =<BR />SUMX (<BR />'table2',<BR />VAR MonthDiff = 'table2'[Months]<BR />VAR MaxOutDate =<BR />CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )<BR />RETURN<BR />SUMX (<BR />FILTER (<BR />'Table1',<BR />DATEDIFF (<BR />'Table1'[year-month New],<BR />COALESCE ( 'Table1'[year-month Out], MaxOutDate ),<BR />MONTH<BR />) &gt;= MonthDiff<BR />),<BR />'Table1'[Quantity]<BR />) + 0<BR />)</P> <P>&nbsp;</P> Sat, 23 Mar 2024 13:49:49 GMT tamerj1 2024-03-23T13:49:49Z Sumarize a Cohort Measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787495#M147952 <P>Hi!&nbsp;I have this measure to do a cohort analysis, but i need to sumarize in totals. One option is to use the <STRONG>variable</STRONG> "Monthdiff", changing the value from 1 to 12 and sumarize at the end.</P><P>&nbsp;</P><P><STRONG>Original Measure:</STRONG></P><P>&nbsp;</P><P>Measure Cohort =<BR />VAR MonthDiff =<BR />MAX ( 'table2'[Months] )<BR />VAR MaxOutDate =<BR />CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )<BR />RETURN<BR />SUMX (<BR />FILTER (<BR />'Table1',<BR />DATEDIFF (<BR />'Table1'[year-month New],<BR />COALESCE ( 'Table1'[year-month Out], MaxOutDate ),<BR />MONTH<BR />) &gt;= MonthDiff<BR />),<BR />'Table1'[Quantity]<BR />) + 0</P><P>&nbsp;</P><P><STRONG>Table1:</STRONG></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>&nbsp; Product&nbsp;&nbsp;</TD><TD>&nbsp; &nbsp; &nbsp;Quantity&nbsp;&nbsp;</TD><TD>&nbsp; year-month New&nbsp;&nbsp;</TD><TD>&nbsp; year-month Out&nbsp;&nbsp;</TD></TR><TR><TD>AA</TD><TD>5</TD><TD>2023-06</TD><TD>&nbsp;</TD></TR><TR><TD>AA</TD><TD>13</TD><TD>2023-05</TD><TD>&nbsp;</TD></TR><TR><TD>AA</TD><TD>1</TD><TD>2023-07</TD><TD>&nbsp;</TD></TR><TR><TD>AA</TD><TD>1</TD><TD>2023-06</TD><TD>2023-11</TD></TR><TR><TD>AA</TD><TD>1</TD><TD>2023-06</TD><TD>2023-10</TD></TR><TR><TD>AA</TD><TD>2</TD><TD>2023-06</TD><TD>2023-10</TD></TR><TR><TD>AA</TD><TD>2</TD><TD>2023-06</TD><TD>2023-09</TD></TR><TR><TD>AA</TD><TD>42</TD><TD>2023-06</TD><TD>2023-09</TD></TR><TR><TD>AA</TD><TD>6</TD><TD>2023-06</TD><TD>2023-08</TD></TR><TR><TD>AA</TD><TD>10</TD><TD>2023-06</TD><TD>2023-08</TD></TR><TR><TD>AA</TD><TD>53</TD><TD>2023-06</TD><TD>2023-07</TD></TR><TR><TD>AA</TD><TD>82</TD><TD>2023-06</TD><TD>2023-07</TD></TR><TR><TD>AA</TD><TD>2</TD><TD>2023-05</TD><TD>2023-07</TD></TR><TR><TD>AA</TD><TD>1</TD><TD>2023-05</TD><TD>2023-06</TD></TR><TR><TD>AA</TD><TD>1</TD><TD>2023-05</TD><TD>2023-06</TD></TR><TR><TD>AA</TD><TD>3</TD><TD>2023-05</TD><TD>2023-06</TD></TR><TR><TD>AA</TD><TD>4</TD><TD>2023-05</TD><TD>2023-06</TD></TR><TR><TD>AA</TD><TD>13</TD><TD>2023-05</TD><TD>2023-05</TD></TR><TR><TD>AA</TD><TD>4</TD><TD>2023-05</TD><TD>2023-05</TD></TR><TR><TD>AA</TD><TD>4</TD><TD>2023-05</TD><TD>2023-10</TD></TR><TR><TD>AA</TD><TD>141</TD><TD>2023-05</TD><TD>2023-09</TD></TR><TR><TD>AA</TD><TD>45</TD><TD>2023-05</TD><TD>2023-09</TD></TR><TR><TD>AA</TD><TD>14</TD><TD>2023-05</TD><TD>2023-08</TD></TR><TR><TD>AA</TD><TD>8</TD><TD>2023-05</TD><TD>2023-08</TD></TR><TR><TD>AA</TD><TD>314</TD><TD>2023-05</TD><TD>2023-10</TD></TR><TR><TD>AA</TD><TD>225</TD><TD>2023-05</TD><TD>2023-11</TD></TR></TBODY></TABLE><DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN><STRONG>table2</STRONG>=</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp;</SPAN><SPAN>GENERATESERIES</SPAN><SPAN> (</SPAN><SPAN>0</SPAN><SPAN>,</SPAN><SPAN>12</SPAN><SPAN>,</SPAN><SPAN>1</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><U><STRONG>Expected Result:</STRONG></U></DIV></DIV></DIV></DIV><P>&nbsp;</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>&nbsp;</TD><TD>&nbsp;1&nbsp;</TD><TD>&nbsp;2&nbsp;</TD><TD>&nbsp;3&nbsp;</TD><TD>&nbsp;4&nbsp;</TD><TD>&nbsp;5&nbsp;</TD><TD>&nbsp;6&nbsp;</TD><TD>&nbsp;7&nbsp;</TD><TD>&nbsp;8&nbsp;&nbsp;</TD><TD>&nbsp;9&nbsp;</TD><TD>&nbsp;10&nbsp;</TD><TD>&nbsp;11&nbsp;</TD><TD>&nbsp;12&nbsp;</TD><TD><STRONG>total</STRONG></TD></TR><TR><TD>&nbsp; 2023-05&nbsp;</TD><TD>&nbsp;775&nbsp;</TD><TD>&nbsp;766&nbsp;</TD><TD>&nbsp;764</TD><TD>&nbsp;742</TD><TD>&nbsp;556</TD><TD>&nbsp;238</TD><TD>&nbsp;13</TD><TD>&nbsp;13</TD><TD>&nbsp;13</TD><TD>&nbsp;13</TD><TD>&nbsp;13</TD><TD>&nbsp;13</TD><TD><STRONG>&nbsp;3919</STRONG></TD></TR><TR><TD>&nbsp; 2023-06&nbsp;</TD><TD>&nbsp;204&nbsp;</TD><TD>&nbsp;69&nbsp;</TD><TD>&nbsp;53</TD><TD>&nbsp;9</TD><TD>&nbsp;6</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD>&nbsp;5</TD><TD><STRONG>&nbsp;376</STRONG></TD></TR><TR><TD>&nbsp; 2023-07&nbsp;</TD><TD>&nbsp;1&nbsp;</TD><TD>&nbsp;1&nbsp;</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD>&nbsp;1</TD><TD><STRONG>&nbsp;12</STRONG></TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD><STRONG>&nbsp;4307</STRONG></TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>&nbsp;</TD><TD><STRONG>Row total</STRONG></TD></TR><TR><TD>2023-05&nbsp;</TD><TD>&nbsp;3919</TD></TR><TR><TD>2023-06&nbsp;</TD><TD>&nbsp;376</TD></TR><TR><TD>2023-07&nbsp;</TD><TD>&nbsp;12</TD></TR><TR><TD><STRONG>TOTAL&nbsp;</STRONG></TD><TD><STRONG>&nbsp;4307</STRONG></TD></TR></TBODY></TABLE> Sat, 23 Mar 2024 13:33:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787495#M147952 pg1980 2024-03-23T13:33:05Z Re: Sumarize a Cohort Measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787497#M147953 <P>I created one measure for each value but it is <U>not usefull</U>. for example:<BR /><BR /><STRONG>Measure Cohort 1</STRONG> =<BR />VAR MonthDiff = <STRONG>1</STRONG><BR />VAR MaxOutDate =<BR />CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )<BR />RETURN<BR />SUMX (<BR />FILTER (<BR />'Table1',<BR />DATEDIFF (<BR />'Table1'[year-month New],<BR />COALESCE ( 'Table1'[year-month Out], MaxOutDate ),<BR />MONTH<BR />) &gt;= MonthDiff<BR />),<BR />'Table1'[Quantity]<BR />) + 0<BR /><BR /><STRONG>Measure Cohort Total</STRONG>= Measure Cohort 1 + Measure Cohort 2 + Measure Cohort 3 + Measure Cohort 4 + Measure Cohort 5 .....</P> Sat, 23 Mar 2024 13:27:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787497#M147953 pg1980 2024-03-23T13:27:14Z Re: Sumarize a Cohort Measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787507#M147956 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="390705" data-lia-user-login="pg1980" class="lia-mention lia-mention-user">pg1980</a>&nbsp;</P> <P><SPAN>you may try</SPAN></P> <P>MeasureCohort =<BR />SUMX (<BR />'table2',<BR />VAR MonthDiff = 'table2'[Months]<BR />VAR MaxOutDate =<BR />CALCULATE ( MAX ( 'Table1'[year-month Out] ), REMOVEFILTERS () )<BR />RETURN<BR />SUMX (<BR />FILTER (<BR />'Table1',<BR />DATEDIFF (<BR />'Table1'[year-month New],<BR />COALESCE ( 'Table1'[year-month Out], MaxOutDate ),<BR />MONTH<BR />) &gt;= MonthDiff<BR />),<BR />'Table1'[Quantity]<BR />) + 0<BR />)</P> <P>&nbsp;</P> Sat, 23 Mar 2024 13:49:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Sumarize-a-Cohort-Measure/m-p/3787507#M147956 tamerj1 2024-03-23T13:49:49Z