Forum Discussion
roling
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
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
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
5 Replies
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
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
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
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
