30day moving average - does it require summarized table?

[hxkresl]

Hello, I'm trying to get 30 day moving avg measure to work without having to first create a summarized table (count, group by date) of source data.  

 Measure that works with summarized table

30 Day Moving Avg =
CALCULATE(
AVERAGEX ('Summary Table (Case Count)', 'Summary Table (Case Count)'[CountofCasesperday]),
    DATESINPERIOD (
        'Summary Table (Case Count)'[EOWeekMonSun],
        LASTDATE ('Summary Table (Case Count)'[EOWeekMonSun]),
        -30,
        DAY
    )
)

Outcome - good

The data points on the line show the average of the last 30 day count of cases.  On Apr 9, moving avg is same as count, Apr 16 it is 4+37 / 2, Apr 23 4 + 37 + 56 / 3, etc.

graph on summarized table

But essentially the same measure on unsummarized data table does not give needed outcome

MovingAvg =
CALCULATE(
AVERAGEX ('Ticket-Details', 'Ticket-Details'[CasesPerDay]),
    DATESINPERIOD (
        'Ticket-Details'[EOWeek],
        LASTDATE ('Ticket-Details'[EOWeek]),
        -30,
        DAY
    )
)

Outcome - not good

Why is measure not working on unsummarized data. Is summarized table only way to go?  

[Eric_Zhang · Accepted solution]

---

@hxkresl wrote:

The data points on the line show the average of the last 30 day count of cases.  On Apr 9, moving avg is same as count, Apr 16 it is 4+37 / 2, Apr 23 4 + 37 + 56 / 3, etc.

---

@hxkresl

Not clear about the underlying data in your table, however according to the description on what "average" is expected in your case, the measure below shall work.

MovingAvg =
DIVIDE (
    CALCULATE (
        SUM ( 'Ticket-Details'[CasesPerDay] ),
        FILTER (
            ALLSELECTED ( 'Ticket-Details' ),
            'Ticket-Details'[EOWeek] <= MAX ( 'Ticket-Details'[EOWeek] )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Ticket-Details'[EOWeek] ),
        FILTER (
            ALLSELECTED ( 'Ticket-Details' ),
            'Ticket-Details'[EOWeek] <= MAX ( 'Ticket-Details'[EOWeek] )
        )
    )
)

[hxkresl]

Thank you  @Eric_Zhang

 I understand you are dividing the total count of cases by the total number of week periods, which is giving me the desired average at the intersection of date and count.  Thank you for showing me.   

I added range to make so only 30 day avg and now it's working perfectly against source data.

30DayMovingAvg_CaseCount =
DIVIDE (
    CALCULATE (
        COUNTA( 'Ticket-Details'[Case #] ),
        FILTER (
            ALLSELECTED ( 'Ticket-Details' ),
            'Ticket-Details'[WeekEnd] <= MAX ( 'Ticket-Details'[WeekEnd] ) && 'Ticket-Details'[WeekEnd] >= MAX ( 'Ticket-Details'[WeekEnd] )-30
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Ticket-Details'[WeekEnd] ),
        FILTER (
            ALLSELECTED ( 'Ticket-Details' ),
            'Ticket-Details'[WeekEnd] <= MAX ( 'Ticket-Details'[WeekEnd] ) && 'Ticket-Details'[WeekEnd] >= MAX ( 'Ticket-Details'[WeekEnd] )-30
        )
    )
)

Ta daah!!!