Forum Discussion

sv248's avatar
sv248
Frequent Visitor
9 years ago
Solved

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 giv...
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

     

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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,