cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hxkresl Member
Member

30day moving average - does it require summarized table?

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.

Capture.PNGgraph 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 goodCapture1.PNG

 

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: 30day moving average - does it require summarized table?


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

View solution in original post

2 REPLIES 2
Moderator Eric_Zhang
Moderator

Re: 30day moving average - does it require summarized table?


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

View solution in original post

hxkresl Member
Member

Re: 30day moving average - does it require summarized table?

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!!!Capture5.PNG

 

 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)