Powerpivot 2016 measure using DAX to sum an array

Question

I want to sum the 7 preceding values of a row as a measure like so:

| Wk_number | Value A | Measure |   Array |
-------------------------------------------
|        01 |       1 |   N/A#  |   N/A#  |
|        02 |       1 |       1 |    {01} |
|        03 |      10 |       2 | {01-02} |  
|        04 |       3 |      12 | {01-03} |
|        05 |       5 |      15 | {01-04} |
|        06 |      10 |      20 | {01-05} |
|        07 |       1 |      30 | {01-06} |
|        08 |       4 |      31 | {01-07} |
|        09 |     -10 |      34 | {02-08} |
|        10 |       3 |      26 | {03-09} |
|        11 |       2 |      18 | {04-10} |
etc...

I added the array column just to clarify the example how of the sum is comprised, notice that from wk09 it's not simply a running total.

How to do this using DAX statements?

Answer 1

Two ways to do this: you can either create a calculated column, or a measure.

For the column:

=CALCULATE(SUM([Value A]),FILTER(Table,Table[Wk_number]<EARLIER(Table[Wk_number]) && Table[Wk_number] >= (EARLIER(Table[Wk_number])-7)))

For the measure, it's a very similar formula but instead of using EARLIER(), we use MAX():

=CALCULATE(SUM([Value A]),FILTER(ALL(Table3),Table3[Wk_number]<MAX(Table3[Wk_number]) && Table3[Wk_number] >= (MAX(Table3[Wk_number])-7)))

Below is a screenshot of the results. A few of the numbers in your example table seem to be off based on the math: