Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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
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
Did the data I provided give more context as to what I was looking for? I'm happy to provide anything else
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 |
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |