Reply
Highlighted
Visitor
Posts: 1
Registered: ‎02-07-2017
Accepted Solution

mat

How would I work out the moving annual trend for quarterly data, given a set of conditions:
i.e.

sum 4 consecutive quarters ( date on this row and previous 3 quarters) of Value A for category A given category B given category C

divided by sum of

sum 4 consecutive quarters (  date on this row and previous 3 quarters) of Value B for category A given category B given category C

 

In excel I normally do a VLOOKUP for the quarters and create an 'index' number. Then use sumifs so that I have my categories and index with their conditions then sum for index, index-1, index-2 and index -3

 

i.e. sum(sumifs( testscores, year group, name of year group, class, class name, index, current date), sumifs( testscores, year group, name of year group, class, class name, index, current date-1), sumifs( testscores, year group, name of year group, class, class name, index, current date-2), sumifs( testscores, year group, name of year group, class, class name, index, current date-3)

 

(and divide by a different value with same conditions if need be)


Accepted Solutions
Moderator
Posts: 1,190
Registered: ‎03-06-2016

Re: mat

@sv248

 

In this scenario, I suggest you use a full calendar table to limit the Previous 3 Quarter context filter. I assume you those Category A, B, C columns and Value A, B columns are in same table. Then you can create a measure like below:

 

[3 Quarter Moving Sum Value A] =
CALCULATE (
    SUM ( Table[ValueA] ),
    DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -3, QUARTER ),
    FILTER (
        ALL ( Table ),
        Table[Category A] = "Category A"
            && Table[Category B] = "Category B"
            && Table[Category C] = "Category C"
    )
)

Regards,

 

View solution in original post


All Replies
Super Contributor
Posts: 901
Registered: ‎11-29-2015

Re: mat

Hi Mat

 

The pattern you should consider is the Moving Average pattern from the link below.  I use it regularly and it works a treat.  Make sure you have a Date table and adjust the table/column names to suit. 

 

http://www.daxpatterns.com/statistical-patterns/

 

Cheers,

 

Phil

Moderator
Posts: 1,190
Registered: ‎03-06-2016

Re: mat

@sv248

 

In this scenario, I suggest you use a full calendar table to limit the Previous 3 Quarter context filter. I assume you those Category A, B, C columns and Value A, B columns are in same table. Then you can create a measure like below:

 

[3 Quarter Moving Sum Value A] =
CALCULATE (
    SUM ( Table[ValueA] ),
    DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -3, QUARTER ),
    FILTER (
        ALL ( Table ),
        Table[Category A] = "Category A"
            && Table[Category B] = "Category B"
            && Table[Category C] = "Category C"
    )
)

Regards,