cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Taking the average of a subset of a rolling sum (DirectQuery)

Hi all,

I have transactional inventory data that I am looking to summarize as a running total by Fiscal Period (a daterange defined by our personal fiscal calendar) and then take the average of the last 13 of those periods.

My end goal is a card visual showing just the average value of the last 13 periods of the running sum. I use a field "PERIODOFFSET" that has a numeric (1, 0, -1, etc) to denote the current fiscal period (0) or 1 period back (-1), etc.

My calculation is like this:

Cumulative TOTALASSETS =
CALCULATE (
SUM ( InventoryTable[TOTALASSETS]),
FILTER (
ALL ( FiscalCalendarTable[PeriodOffset] ),
FiscalCalendarTable[PeriodOffset] <= IF(FiscalCalendarTable[PeriodOffset] <= -1, MAX(FiscalCalendarTable[PeriodOffset] ),-1))
)

Basically I'd like the values that it returns from PERIODOFFSET -1 to -13 (representing the last 13 periods) and then average them. This calculation gives me the sum for each period, but only if each period is in the view. My goal is a single number card.

Any ideas?
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Taking the average of a subset of a rolling sum

hi, @BSLATTER

Sorry for late reply, You could use these two formulae to create two measure

`Cumulative sum = CALCULATE(SUM(transactional[Amount]),FILTER(ALLSELECTED('calendar'),calendar[Period Offset]<=MAX(calendar[Period Offset])))`
```Average of Last 13 Periods = var _table=SUMMARIZE('calendar','calendar'[Period Offset],"CAmount", [Cumulative sum]) return
CALCULATE(AVERAGEX(FILTER(_table,[Period Offset]>=-13&&[Period Offset]<=-1),[CAmount]))```

Result:

and here is pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

## Re: Taking the average of a subset of a rolling sum

hi, @BSLATTER

it returns from PERIODOFFSET -1 to -13

If you need to add a conditional like red part as  below:

```Cumulative TOTALASSETS =
CALCULATE (
SUM ( InventoryTable[TOTALASSETS] ),
FILTER (
ALL ( FiscalCalendarTable[PeriodOffset] ),
FiscalCalendarTable[PeriodOffset]
<= IF (
FiscalCalendarTable[PeriodOffset] <= -1,
MAX ( FiscalCalendarTable[PeriodOffset] ),
-1
)
&& FiscalCalendarTable[PeriodOffset] >= -13
)
)```

Share your sample pbix file or some sample data and expected output.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Taking the average of a subset of a rolling sum

Hello,

I am posting some sample data below. It is two tables - one transactional, and one showing how the fiscal calendar would be approximately setup. I'm also including a picture of the expected output (The "Average of Last 13 Periods" is my hopeful output)

 Date Trans ID Amount 13-Jun-17 1 115 2-Jul-17 20 57 9-Jul-17 27 81 11-Jul-17 29 19 18-Jul-17 36 19 23-Jul-17 41 109 27-Jul-17 45 139 1-Aug-17 50 101 2-Aug-17 51 119 5-Aug-17 54 49 6-Aug-17 55 6 19-Aug-17 68 56 31-Aug-17 80 85 6-Sep-17 86 58 7-Sep-17 87 84 17-Sep-17 97 104 23-Sep-17 103 87 25-Sep-17 105 146 29-Sep-17 109 54 30-Sep-17 110 51 3-Oct-17 113 93 4-Oct-17 114 74 12-Oct-17 122 81 16-Oct-17 126 39 21-Oct-17 131 143 24-Oct-17 134 123 26-Oct-17 136 90 6-Nov-17 147 14 22-Nov-17 163 128 5-Dec-17 176 14 9-Dec-17 180 32 11-Dec-17 182 9 13-Dec-17 184 7 18-Dec-17 189 4 24-Dec-17 195 64 30-Dec-17 201 121 8-Jan-18 210 15 15-Jan-18 217 128 27-Jan-18 229 19 1-Feb-18 234 132 13-Feb-18 246 150 19-Feb-18 252 75 21-Feb-18 254 84 23-Feb-18 256 80 8-Mar-18 269 90 10-Mar-18 271 146 16-Mar-18 277 108 21-Mar-18 282 109 22-Mar-18 283 113 31-Mar-18 292 108 4-Apr-18 296 133 7-May-18 329 147 10-May-18 332 99 12-May-18 334 47 14-May-18 336 38 19-May-18 341 103 31-May-18 353 107 5-Jun-18 358 66 7-Jun-18 360 2 12-Jun-18 365 39 16-Jun-18 369 86 20-Jun-18 373 73 24-Jun-18 377 60 27-Jun-18 380 94 29-Jun-18 382 60 6-Jul-18 389 63 11-Jul-18 394 25 14-Jul-18 397 121 18-Jul-18 401 61 19-Jul-18 402 79 25-Jul-18 408 76 28-Jul-18 411 90 2-Aug-18 416 23 3-Aug-18 417 114 7-Aug-18 421 119 8-Aug-18 422 108 15-Aug-18 429 51 17-Aug-18 431 90 20-Aug-18 434 70 22-Aug-18 436 40 31-Aug-18 445 17 5-Sep-18 450 134 9-Sep-18 454 93 10-Sep-18 455 96 11-Sep-18 456 48 16-Sep-18 461 72 17-Sep-18 462 64 19-Sep-18 464 34 23-Sep-18 468 28 27-Sep-18 472 63 28-Sep-18 473 71 30-Sep-18 475 85 2-Oct-18 477 95 10-Oct-18 485 66 17-Oct-18 492 128 21-Oct-18 496 4 23-Oct-18 498 77 24-Oct-18 499 97 27-Oct-18 502 136 29-Oct-18 504 53 31-Oct-18 506 112 12-Nov-18 518 42 15-Nov-18 521 124 18-Nov-18 524 120 19-Nov-18 525 34 27-Nov-18 533 72 3-Dec-18 539 7 4-Dec-18 540 112 12-Dec-18 548 4 13-Dec-18 549 24 15-Dec-18 551 118 27-Dec-18 563 16 1-Jan-19 568 130 2-Jan-19 569 27 3-Jan-19 570 92 5-Jan-19 572 116 6-Jan-19 573 83 12-Jan-19 579 65 24-Jan-19 591 85 29-Jan-19 596 130

 Date Period Fiscal Year Period Offset 13-Jun-17 Period 6 2017 -21 2-Jul-17 Period 7 2017 -20 9-Jul-17 Period 7 2017 -20 11-Jul-17 Period 7 2017 -20 18-Jul-17 Period 7 2017 -20 23-Jul-17 Period 7 2017 -20 27-Jul-17 Period 7 2017 -20 1-Aug-17 Period 8 2017 -19 2-Aug-17 Period 8 2017 -19 5-Aug-17 Period 8 2017 -19 6-Aug-17 Period 8 2017 -19 19-Aug-17 Period 8 2017 -19 31-Aug-17 Period 9 2017 -18 6-Sep-17 Period 9 2017 -18 7-Sep-17 Period 9 2017 -18 17-Sep-17 Period 9 2017 -18 23-Sep-17 Period 9 2017 -18 25-Sep-17 Period 9 2017 -18 29-Sep-17 Period 10 2017 -17 30-Sep-17 Period 10 2017 -17 3-Oct-17 Period 10 2017 -17 4-Oct-17 Period 10 2017 -17 12-Oct-17 Period 10 2017 -17 16-Oct-17 Period 10 2017 -17 21-Oct-17 Period 10 2017 -17 24-Oct-17 Period 10 2017 -17 26-Oct-17 Period 11 2017 -16 6-Nov-17 Period 11 2017 -16 22-Nov-17 Period 12 2017 -15 5-Dec-17 Period 13 2017 -14 9-Dec-17 Period 13 2017 -14 11-Dec-17 Period 13 2017 -14 13-Dec-17 Period 13 2017 -14 18-Dec-17 Period 13 2017 -14 24-Dec-17 Period 13 2017 -14 30-Dec-17 Period 13 2017 -14 8-Jan-18 Period 1 2018 -13 15-Jan-18 Period 1 2018 -13 27-Jan-18 Period 2 2018 -12 1-Feb-18 Period 2 2018 -12 13-Feb-18 Period 2 2018 -12 19-Feb-18 Period 2 2018 -12 21-Feb-18 Period 2 2018 -12 23-Feb-18 Period 2 2018 -12 8-Mar-18 Period 2 2018 -12 10-Mar-18 Period 2 2018 -12 16-Mar-18 Period 2 2018 -12 21-Mar-18 Period 2 2018 -12 22-Mar-18 Period 2 2018 -12 31-Mar-18 Period 3 2018 -11 4-Apr-18 Period 3 2018 -11 7-May-18 Period 4 2018 -10 10-May-18 Period 4 2018 -10 12-May-18 Period 4 2018 -10 14-May-18 Period 4 2018 -10 19-May-18 Period 4 2018 -10 31-May-18 Period 5 2018 -9 5-Jun-18 Period 5 2018 -9 7-Jun-18 Period 5 2018 -9 12-Jun-18 Period 5 2018 -9 16-Jun-18 Period 5 2018 -9 20-Jun-18 Period 5 2018 -9 24-Jun-18 Period 6 2018 -8 27-Jun-18 Period 6 2018 -8 29-Jun-18 Period 6 2018 -8 6-Jul-18 Period 6 2018 -8 11-Jul-18 Period 6 2018 -8 14-Jul-18 Period 6 2018 -8 18-Jul-18 Period 6 2018 -8 19-Jul-18 Period 6 2018 -8 25-Jul-18 Period 7 2018 -7 28-Jul-18 Period 7 2018 -7 2-Aug-18 Period 7 2018 -7 3-Aug-18 Period 7 2018 -7 7-Aug-18 Period 7 2018 -7 8-Aug-18 Period 7 2018 -7 15-Aug-18 Period 8 2018 -6 17-Aug-18 Period 8 2018 -6 20-Aug-18 Period 8 2018 -6 22-Aug-18 Period 8 2018 -6 31-Aug-18 Period 8 2018 -6 5-Sep-18 Period 8 2018 -6 9-Sep-18 Period 9 2018 -5 10-Sep-18 Period 9 2018 -5 11-Sep-18 Period 9 2018 -5 16-Sep-18 Period 9 2018 -5 17-Sep-18 Period 9 2018 -5 19-Sep-18 Period 9 2018 -5 23-Sep-18 Period 9 2018 -5 27-Sep-18 Period 9 2018 -5 28-Sep-18 Period 9 2018 -5 30-Sep-18 Period 9 2018 -5 2-Oct-18 Period 9 2018 -5 10-Oct-18 Period 10 2018 -4 17-Oct-18 Period 10 2018 -4 21-Oct-18 Period 10 2018 -4 23-Oct-18 Period 10 2018 -4 24-Oct-18 Period 10 2018 -4 27-Oct-18 Period 10 2018 -4 29-Oct-18 Period 10 2018 -4 31-Oct-18 Period 10 2018 -4 12-Nov-18 Period 11 2018 -3 15-Nov-18 Period 11 2018 -3 18-Nov-18 Period 11 2018 -3 19-Nov-18 Period 11 2018 -3 27-Nov-18 Period 11 2018 -3 3-Dec-18 Period 11 2018 -3 4-Dec-18 Period 11 2018 -3 12-Dec-18 Period 12 2018 -2 13-Dec-18 Period 12 2018 -2 15-Dec-18 Period 12 2018 -2 27-Dec-18 Period 13 2018 -1 1-Jan-19 Period 13 2018 -1 2-Jan-19 Period 13 2018 -1 3-Jan-19 Period 13 2019 -1 5-Jan-19 Period 13 2019 -1 6-Jan-19 Period 1 2019 0 12-Jan-19 Period 1 2019 0 24-Jan-19 Period 1 2019 0 29-Jan-19 Period 1 2019 0

Frequent Visitor

## Re: Taking the average of a subset of a rolling sum

Did the data I  provided give more context as to what I was looking for? I'm happy to provide anything else

Community Support Team

## Re: Taking the average of a subset of a rolling sum

hi, @BSLATTER

Sorry for late reply, You could use these two formulae to create two measure

`Cumulative sum = CALCULATE(SUM(transactional[Amount]),FILTER(ALLSELECTED('calendar'),calendar[Period Offset]<=MAX(calendar[Period Offset])))`
```Average of Last 13 Periods = var _table=SUMMARIZE('calendar','calendar'[Period Offset],"CAmount", [Cumulative sum]) return
CALCULATE(AVERAGEX(FILTER(_table,[Period Offset]>=-13&&[Period Offset]<=-1),[CAmount]))```

Result:

and here is pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.