roling

[rajannaap]

Hi ,

i need simillar help on my data

i have colums like 

Period        divsion   segment    net

201501          a              1              12332

201502          b              2              21233

..                    ...             ....               ......

201812          a              2              312333

I need to calcuate avg  net value (from 201701 to 201712)  for each division and segment by skipping recent 12 periods say (201801 to 201812)

your help would be appreciated.

Thanks

[v-frfei-msft · Accepted solution]

Hi @rajannaap,

I made one sample for your reference based on your sample data as you shared.  If I misunderstand, kindly share more about that. Here I created a measure to get the avg.

Measure = var mindate = TODAY()-730
var maxdate = TODAY()-365 
var minper = YEAR(mindate)*100+MONTH(mindate)
var maxper = YEAR(maxdate)*100+MONTH(maxdate)
var net = CALCULATE(SUM(Table1[net]),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
return
net/countr

Then you can filter the slicer to get the avg you need.

For more details, please check the pbix as attached.

Regards,

Frank

[rajannaap]

Hi All, I am trying to get a 12 months NET rolling avg (by skipping recent 12 months’ data, and calculating avg for prior 12 months) by each division and segment. Request to provide a solution. Division segment period NET TV 1 201204 728 TV 1 201205 3,978 TV 1 201206 6,256 TV 1 201207 11,871 TV 1 201208 16,279 TV 1 201209 18,429 TV 1 201210 22,689 TV 1 201211 26,262 TV 1 201212 28,704 TV 1 201301 31,029 TV 1 201302 32,072 TV 1 201303 34,724 TV 1 201304 39,631 TV 1 201305 44,762 TV 1 201306 45,472 TV 1 201307 52,898 TV 1 201308 55,205 TV 1 201309 59,604 TV 1 201310 64,538 TV 1 201311 59,445 TV 1 201312 66,503 TV 1 201401 73,671 TV 1 201402 77,539 TV 1 201403 87,526 TV 1 201404 97,725 TV 1 201405 103,724 TV 1 201406 111,131 TV 1 201407 119,748 TV 1 201408 117,246 TV 1 201409 130,807 TV 1 201410 137,673 TV 1 201411 141,101 TV 1 201412 150,357 TV 1 201501 155,458 TV 1 201502 158,300 TV 1 201503 179,181 TV 1 201504 192,991 TV 1 201505 199,962 TV 1 201506 216,752 TV 1 201507 232,360 TV 1 201508 242,427 TV 1 201509 251,135 TV 1 201510 259,636 TV 1 201511 259,318 TV 1 201512 267,626 TV 1 201601 265,668 TV 1 201602 276,450 TV 1 201603 291,388 TV 1 201604 296,706 TV 1 201605 308,020 TV 1 201606 313,721 TV 1 201607 308,500 TV 1 201608 318,015 TV 1 201609 312,424 TV 1 201610 301,441 TV 1 201611 285,888 TV 1 201612 274,147 TV 1 201701 261,671 TV 1 201702 244,184 TV 1 201703 240,919 TV 1 201704 224,804 TV 1 201705 218,531 TV 1 201706 209,992 TV 1 201707 199,850 TV 1 201708 193,722 TV 1 201709 181,462 TV 1 201710 175,345 TV 1 201711 165,727 TV 1 201712 154,080 TV 1 201801 150,482 TV 1 201802 141,008 TV 1 201803 135,152 TV 1 201804 126,533 TV 1 201805 121,275 TV 1 201806 114,836 TV 1 201807 107,067 TV 1 201808 100,317 TV 2 201204 282 TV 2 201205 1,540 TV 2 201206 2,422 TV 2 201207 4,595 TV 2 201208 6,302 TV 2 201209 7,134 TV 2 201210 8,783 TV 2 201211 10,166 TV 2 201212 11,111 TV 2 201301 12,011 TV 2 201302 12,415 TV 2 201303 13,441 TV 2 201304 15,341 TV 2 201305 17,327 TV 2 201306 17,602 TV 2 201307 20,477 TV 2 201308 21,370 TV 2 201309 23,073 TV 2 201310 24,983 TV 2 201311 23,011 TV 2 201312 25,743 TV 2 201401 28,518 TV 2 201402 30,015 TV 2 201403 33,881 TV 2 201404 37,829 TV 2 201405 40,151 TV 2 201406 43,019 TV 2 201407 46,354 TV 2 201408 45,385 TV 2 201409 50,635 TV 2 201410 53,293 TV 2 201411 54,620 TV 2 201412 58,203 TV 2 201501 60,177 TV 2 201502 61,277 TV 2 201503 69,360 TV 2 201504 74,706 TV 2 201505 77,405 TV 2 201506 83,904 TV 2 201507 89,946 TV 2 201508 93,843 TV 2 201509 97,213 TV 2 201510 100,504 TV 2 201511 100,381 TV 2 201512 103,597 TV 2 201601 102,839 TV 2 201602 107,013 TV 2 201603 112,795 TV 2 201604 114,854 TV 2 201605 119,233 TV 2 201606 121,441 TV 2 201607 119,419 TV 2 201608 123,102 TV 2 201609 120,938 TV 2 201610 116,687 TV 2 201611 110,666 TV 2 201612 106,121 TV 2 201701 101,292 TV 2 201702 94,523 TV 2 201703 93,259 TV 2 201704 87,021 TV 2 201705 84,593 TV 2 201706 81,287 TV 2 201707 77,361 TV 2 201708 74,989 TV 2 201709 70,243 TV 2 201710 67,875 TV 2 201711 64,152 TV 2 201712 59,644 TV 2 201801 58,251 TV 2 201802 54,584 TV 2 201803 52,317 TV 2 201804 48,980 TV 2 201805 46,945 TV 2 201806 44,453 TV 2 201807 41,445 TV 2 201808 38,832 DTH 1 201204 340 DTH 1 201205 1,856 DTH 1 201206 2,919 DTH 1 201207 5,540 DTH 1 201208 7,597 DTH 1 201209 8,600 DTH 1 201210 10,588 DTH 1 201211 12,256 DTH 1 201212 13,395 DTH 1 201301 14,480 DTH 1 201302 14,967 DTH 1 201303 16,204 DTH 1 201304 18,494 DTH 1 201305 20,889 DTH 1 201306 21,220 DTH 1 201307 24,686 DTH 1 201308 25,762 DTH 1 201309 27,815 DTH 1 201310 30,118 DTH 1 201311 27,741 DTH 1 201312 31,035 DTH 1 201401 34,380 DTH 1 201402 36,185 DTH 1 201403 40,845 DTH 1 201404 45,605 DTH 1 201405 48,404 DTH 1 201406 51,861 DTH 1 201407 55,882 DTH 1 201408 54,715 DTH 1 201409 61,043 DTH 1 201410 64,247 DTH 1 201411 65,847 DTH 1 201412 70,166 DTH 1 201501 72,547 DTH 1 201502 73,873 DTH 1 201503 83,618 DTH 1 201504 90,062 DTH 1 201505 93,315 DTH 1 201506 101,151 DTH 1 201507 108,435 DTH 1 201508 113,133 DTH 1 201509 117,196 DTH 1 201510 121,163 DTH 1 201511 121,015 DTH 1 201512 124,892 DTH 1 201601 123,979 DTH 1 201602 129,010 DTH 1 201603 135,981 DTH 1 201604 138,463 DTH 1 201605 143,743 DTH 1 201606 146,403 DTH 1 201607 143,967 DTH 1 201608 148,407 DTH 1 201609 145,798 DTH 1 201610 140,672 DTH 1 201611 133,415 DTH 1 201612 127,935 DTH 1 201701 122,113 DTH 1 201702 113,953 DTH 1 201703 112,429 DTH 1 201704 104,909 DTH 1 201705 101,981 DTH 1 201706 97,996 DTH 1 201707 93,263 DTH 1 201708 90,404 DTH 1 201709 84,682 DTH 1 201710 81,827 DTH 1 201711 77,339 DTH 1 201712 71,904 DTH 1 201801 70,225 DTH 1 201802 65,804 DTH 1 201803 63,071 DTH 1 201804 59,049 DTH 1 201805 56,595 DTH 1 201806 53,590 DTH 1 201807 49,964 DTH 1 201808 46,815 DTH 2 201204 132 DTH 2 201205 719 DTH 2 201206 1,130 DTH 2 201207 2,145 DTH 2 201208 2,941 DTH 2 201209 3,329 DTH 2 201210 4,099 DTH 2 201211 4,744 DTH 2 201212 5,185 DTH 2 201301 5,605 DTH 2 201302 5,794 DTH 2 201303 6,273 DTH 2 201304 7,159 DTH 2 201305 8,086 DTH 2 201306 8,214 DTH 2 201307 9,556 DTH 2 201308 9,972 DTH 2 201309 10,767 DTH 2 201310 11,659 DTH 2 201311 10,739 DTH 2 201312 12,014 DTH 2 201401 13,308 DTH 2 201402 14,007 DTH 2 201403 15,811 DTH 2 201404 17,654 DTH 2 201405 18,737 DTH 2 201406 20,075 DTH 2 201407 21,632 DTH 2 201408 21,180 DTH 2 201409 23,630 DTH 2 201410 24,870 DTH 2 201411 25,489 DTH 2 201412 27,161 DTH 2 201501 28,083 DTH 2 201502 28,596 DTH 2 201503 32,368 DTH 2 201504 34,863 DTH 2 201505 36,122 DTH 2 201506 39,155 DTH 2 201507 41,975 DTH 2 201508 43,793 DTH 2 201509 45,366 DTH 2 201510 46,902 DTH 2 201511 46,844 DTH 2 201512 48,345 DTH 2 201601 47,992 DTH 2 201602 49,939 DTH 2 201603 52,638 DTH 2 201604 53,599 DTH 2 201605 55,642 DTH 2 201606 56,672 DTH 2 201607 55,729 DTH 2 201608 57,448 DTH 2 201609 56,438 DTH 2 201610 54,454 DTH 2 201611 51,644 DTH 2 201612 49,523 DTH 2 201701 47,270 DTH 2 201702 44,111 DTH 2 201703 43,521 DTH 2 201704 40,610 DTH 2 201705 39,477 DTH 2 201706 37,934 DTH 2 201707 36,102 DTH 2 201708 34,995 DTH 2 201709 32,780 DTH 2 201710 31,675 DTH 2 201711 29,938 DTH 2 201712 27,834 DTH 2 201801 27,184 DTH 2 201802 25,472 DTH 2 201803 24,415 DTH 2 201804 22,858 DTH 2 201805 21,908 DTH 2 201806 20,745 DTH 2 201807 19,341 DTH 2 201808 18,122

[v-frfei-msft · Accepted solution]

Hi @rajannaap,

I made one sample for your reference based on your sample data as you shared.  If I misunderstand, kindly share more about that. Here I created a measure to get the avg.

Measure = var mindate = TODAY()-730
var maxdate = TODAY()-365 
var minper = YEAR(mindate)*100+MONTH(mindate)
var maxper = YEAR(maxdate)*100+MONTH(maxdate)
var net = CALCULATE(SUM(Table1[net]),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
return
net/countr

Then you can filter the slicer to get the avg you need.

For more details, please check the pbix as attached.

Regards,

Frank

[rajannaap]

Hi v-frfei-msft,

Thank you for your support and i am able to get the desired output.

I have new question, which is based on dynamic value adjustment.

once i get the net avg values for each of the divsion and segment, i need to multiply the calcauted avg values of the some of the segments with the constat value. can we create a cells in power Bi so that user can enter the constant values , so that client can get the expected result.

Thanks

Rajanna

[v-frfei-msft · Accepted solution]

Hi @rajannaap,

We can add a what if parameter to work on it. Then create another new measure.

result = [Measure]*Parameter[Parameter Value]

Regards,

Frank

[rajannaap]

Thanh you v-frfei-msft