Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BSLATTER
Helper III
Helper III

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

‎02-25-2019 01:22 PM

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?
Labels:
Message 1 of 5
448 Views
0
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support
In response to BSLATTER

‎02-27-2019 05:56 PM

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.

View solution in original post

Taking the average of a subset of a rolling sum.pbix
Message 4 of 5
409 Views
0
4 REPLIES 4
v-lili6-msft
Community Support
Community Support

‎02-25-2019 11:37 PM

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.
Message 2 of 5
436 Views
0
BSLATTER
Helper III
Helper III
In response to v-lili6-msft

‎02-27-2019 06:09 AM

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

Message 5 of 5
414 Views
0
BSLATTER
Helper III
Helper III
In response to v-lili6-msft

‎02-26-2019 08:25 AM

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

Message 3 of 5
428 Views
0
v-lili6-msft
Community Support
Community Support
In response to BSLATTER

‎02-27-2019 05:56 PM

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.
Taking the average of a subset of a rolling sum.pbix
Message 4 of 5
410 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All