cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BSLATTER Frequent Visitor
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
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:

7.JPG

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

If not your case, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.
BSLATTER Frequent Visitor
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)

 

DateTrans IDAmount
13-Jun-171115
2-Jul-172057
9-Jul-172781
11-Jul-172919
18-Jul-173619
23-Jul-1741109
27-Jul-1745139
1-Aug-1750101
2-Aug-1751119
5-Aug-175449
6-Aug-17556
19-Aug-176856
31-Aug-178085
6-Sep-178658
7-Sep-178784
17-Sep-1797104
23-Sep-1710387
25-Sep-17105146
29-Sep-1710954
30-Sep-1711051
3-Oct-1711393
4-Oct-1711474
12-Oct-1712281
16-Oct-1712639
21-Oct-17131143
24-Oct-17134123
26-Oct-1713690
6-Nov-1714714
22-Nov-17163128
5-Dec-1717614
9-Dec-1718032
11-Dec-171829
13-Dec-171847
18-Dec-171894
24-Dec-1719564
30-Dec-17201121
8-Jan-1821015
15-Jan-18217128
27-Jan-1822919
1-Feb-18234132
13-Feb-18246150
19-Feb-1825275
21-Feb-1825484
23-Feb-1825680
8-Mar-1826990
10-Mar-18271146
16-Mar-18277108
21-Mar-18282109
22-Mar-18283113
31-Mar-18292108
4-Apr-18296133
7-May-18329147
10-May-1833299
12-May-1833447
14-May-1833638
19-May-18341103
31-May-18353107
5-Jun-1835866
7-Jun-183602
12-Jun-1836539
16-Jun-1836986
20-Jun-1837373
24-Jun-1837760
27-Jun-1838094
29-Jun-1838260
6-Jul-1838963
11-Jul-1839425
14-Jul-18397121
18-Jul-1840161
19-Jul-1840279
25-Jul-1840876
28-Jul-1841190
2-Aug-1841623
3-Aug-18417114
7-Aug-18421119
8-Aug-18422108
15-Aug-1842951
17-Aug-1843190
20-Aug-1843470
22-Aug-1843640
31-Aug-1844517
5-Sep-18450134
9-Sep-1845493
10-Sep-1845596
11-Sep-1845648
16-Sep-1846172
17-Sep-1846264
19-Sep-1846434
23-Sep-1846828
27-Sep-1847263
28-Sep-1847371
30-Sep-1847585
2-Oct-1847795
10-Oct-1848566
17-Oct-18492128
21-Oct-184964
23-Oct-1849877
24-Oct-1849997
27-Oct-18502136
29-Oct-1850453
31-Oct-18506112
12-Nov-1851842
15-Nov-18521124
18-Nov-18524120
19-Nov-1852534
27-Nov-1853372
3-Dec-185397
4-Dec-18540112
12-Dec-185484
13-Dec-1854924
15-Dec-18551118
27-Dec-1856316
1-Jan-19568130
2-Jan-1956927
3-Jan-1957092
5-Jan-19572116
6-Jan-1957383
12-Jan-1957965
24-Jan-1959185
29-Jan-19596130

 

DatePeriodFiscal YearPeriod Offset
13-Jun-17Period 62017-21
2-Jul-17Period 72017-20
9-Jul-17Period 72017-20
11-Jul-17Period 72017-20
18-Jul-17Period 72017-20
23-Jul-17Period 72017-20
27-Jul-17Period 72017-20
1-Aug-17Period 82017-19
2-Aug-17Period 82017-19
5-Aug-17Period 82017-19
6-Aug-17Period 82017-19
19-Aug-17Period 82017-19
31-Aug-17Period 92017-18
6-Sep-17Period 92017-18
7-Sep-17Period 92017-18
17-Sep-17Period 92017-18
23-Sep-17Period 92017-18
25-Sep-17Period 92017-18
29-Sep-17Period 102017-17
30-Sep-17Period 102017-17
3-Oct-17Period 102017-17
4-Oct-17Period 102017-17
12-Oct-17Period 102017-17
16-Oct-17Period 102017-17
21-Oct-17Period 102017-17
24-Oct-17Period 102017-17
26-Oct-17Period 112017-16
6-Nov-17Period 112017-16
22-Nov-17Period 122017-15
5-Dec-17Period 132017-14
9-Dec-17Period 132017-14
11-Dec-17Period 132017-14
13-Dec-17Period 132017-14
18-Dec-17Period 132017-14
24-Dec-17Period 132017-14
30-Dec-17Period 132017-14
8-Jan-18Period 12018-13
15-Jan-18Period 12018-13
27-Jan-18Period 22018-12
1-Feb-18Period 22018-12
13-Feb-18Period 22018-12
19-Feb-18Period 22018-12
21-Feb-18Period 22018-12
23-Feb-18Period 22018-12
8-Mar-18Period 22018-12
10-Mar-18Period 22018-12
16-Mar-18Period 22018-12
21-Mar-18Period 22018-12
22-Mar-18Period 22018-12
31-Mar-18Period 32018-11
4-Apr-18Period 32018-11
7-May-18Period 42018-10
10-May-18Period 42018-10
12-May-18Period 42018-10
14-May-18Period 42018-10
19-May-18Period 42018-10
31-May-18Period 52018-9
5-Jun-18Period 52018-9
7-Jun-18Period 52018-9
12-Jun-18Period 52018-9
16-Jun-18Period 52018-9
20-Jun-18Period 52018-9
24-Jun-18Period 62018-8
27-Jun-18Period 62018-8
29-Jun-18Period 62018-8
6-Jul-18Period 62018-8
11-Jul-18Period 62018-8
14-Jul-18Period 62018-8
18-Jul-18Period 62018-8
19-Jul-18Period 62018-8
25-Jul-18Period 72018-7
28-Jul-18Period 72018-7
2-Aug-18Period 72018-7
3-Aug-18Period 72018-7
7-Aug-18Period 72018-7
8-Aug-18Period 72018-7
15-Aug-18Period 82018-6
17-Aug-18Period 82018-6
20-Aug-18Period 82018-6
22-Aug-18Period 82018-6
31-Aug-18Period 82018-6
5-Sep-18Period 82018-6
9-Sep-18Period 92018-5
10-Sep-18Period 92018-5
11-Sep-18Period 92018-5
16-Sep-18Period 92018-5
17-Sep-18Period 92018-5
19-Sep-18Period 92018-5
23-Sep-18Period 92018-5
27-Sep-18Period 92018-5
28-Sep-18Period 92018-5
30-Sep-18Period 92018-5
2-Oct-18Period 92018-5
10-Oct-18Period 102018-4
17-Oct-18Period 102018-4
21-Oct-18Period 102018-4
23-Oct-18Period 102018-4
24-Oct-18Period 102018-4
27-Oct-18Period 102018-4
29-Oct-18Period 102018-4
31-Oct-18Period 102018-4
12-Nov-18Period 112018-3
15-Nov-18Period 112018-3
18-Nov-18Period 112018-3
19-Nov-18Period 112018-3
27-Nov-18Period 112018-3
3-Dec-18Period 112018-3
4-Dec-18Period 112018-3
12-Dec-18Period 122018-2
13-Dec-18Period 122018-2
15-Dec-18Period 122018-2
27-Dec-18Period 132018-1
1-Jan-19Period 132018-1
2-Jan-19Period 132018-1
3-Jan-19Period 132019-1
5-Jan-19Period 132019-1
6-Jan-19Period 120190
12-Jan-19Period 120190
24-Jan-19Period 120190
29-Jan-19Period 120190

 

image.png

BSLATTER Frequent Visitor
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
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:

7.JPG

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.