Rolling Count Formula

Question

Hi all ,&nbsp;I have this Rolling count formula for the previous 6 months. But the numbers dont match and I dont know why.&nbsp;Sol Provided Cases 6MM = VAR pre6 =&nbsp; &nbsp; CALCULATE(EOMONTH( MAX ( 'All_BGs-Cases_1'[Solution Provided Date]), -6 ))VAR cure = &nbsp; &nbsp; CALCULATE(EOMONTH( MAX ( 'All_BGs-Cases_1'[Solution Provided Date]), 0 )) &nbsp; &nbsp;RETURN&nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; Count('All_BGs-Cases_1'[Support Ticket Number]),&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALLEXCEPT ( 'All_BGs-Cases_1','All_BGs-Cases_1'[cbt],'All_BGs-Cases_1'[Severity],'All_BGs-Cases_1'[legalaccountname],'All_BGs-Cases_1'[Service Type],Product_Group_Lookup[Product Name]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'All_BGs-Cases_1'[Solution Provided Date] &gt; pre6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'All_BGs-Cases_1'[Solution Provided Date] &lt;= cure&nbsp; &nbsp; &nbsp; &nbsp; ),'All_BGs-Cases_1'[Case Milestone_Final_Sol_Violated]&lt;&gt;blank(),'All_BGs-Cases_1'[final_sol_target_date]&lt;&gt;Blank())&nbsp;But as you can see there are some months that the sum/count is not done correctly .&nbsp;Can anyone help ?

amitchandak · Answer

Anonymous , I usually prefer to join the key date with date table and use columns from date table in visual, measure and slicer and formula like 
&nbsp;
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))
&nbsp;
Rolling 6 = CALCULATE([Net], WINDOW(-5,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc))) 
&nbsp;
Rolling Months Formula: https://youtu.be/GS5O4G81fww
&nbsp;
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTchttps://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Forum Discussion

Rolling Count Formula

1 Reply

Related content

Count formula

Rolling accumulation

Rolling accumulation

If formula

formula