Rolling Count Formula

[Anonymous]

Hi all ,

I have this Rolling count formula for the previous 6 months. But the numbers dont match and I dont know why.

Sol Provided Cases 6MM =

VAR pre6 =

    CALCULATE(EOMONTH( MAX ( 'All_BGs-Cases_1'[Solution Provided Date]), -6 ))

VAR cure =

    CALCULATE(EOMONTH( MAX ( 'All_BGs-Cases_1'[Solution Provided Date]), 0 ))

RETURN

   CALCULATE (

      Count('All_BGs-Cases_1'[Support Ticket Number]),

        FILTER (

           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]),

          'All_BGs-Cases_1'[Solution Provided Date] > pre6

                && 'All_BGs-Cases_1'[Solution Provided Date] <= cure

        ),'All_BGs-Cases_1'[Case Milestone_Final_Sol_Violated]<>blank(),'All_BGs-Cases_1'[final_sol_target_date]<>Blank())

But as you can see there are some months that the sum/count is not done correctly .

Can anyone help ?

[amitchandak]

@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

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))

Rolling 6 = CALCULATE([Net], WINDOW(-5,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f