Divide Calculation - 6 month average wrong but why?

Hi Anonymous 

I think you want to calculate the percent by divide count of per month ID and sum of quantity rolling 6 month. I build three tables to have a test.

Table1:

Table2:

Build a calendar table and build relationships between Table1 and CalendarTable 's Date column.

calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

Build a measure to achieve your goal.

Rolling Average 6 Months =
VAR Dos =
    CALCULATE (
        COUNT ( Table2[ID] ),
        FILTER (
            Table2,
            Table2[Year] = MAX ( 'calendar'[Year] )
                && Table2[Month] = MAX ( 'calendar'[Month] )
        )
    )
VAR Count_of_Parts =
    CALCULATE (
        SUM ( 'Table1'[Quantity] ),
        DATESINPERIOD ( 'calendar'[Date], FIRSTDATE ( 'calendar'[Date] ), 6, MONTH )
    )
RETURN
    DIVIDE ( Dos, Count_of_Parts )

Result:

You can download the pbix file from this link: Divide Calculation - 6 month average wrong but why

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.