Forum Discussion
Calculating growth & Rearranging data lables
Hi Luis,
My fiscal year is "April to March". When I am looking at FY 16 on a bar chart which is supposed to be from April 15 to March 16, since I do not have a year along with my month it displays January to March before April which will give the wrong view as it is January 16, not January 15. I tried adding year to the months but PBI again displays the axis labels on the chart as "01-01-2016" instead of "January 2016"
I want to compare April 14 with June 16. I want the growth split by customer too. So if I want the growth for all customers, I get a value but if I want to slice it to display only for customer ABC, I should get the growth for customer ABC too.
-Shruti
Hi shrmehta,
Yes, I think this could be done.
We need another column to define your fisnal Year, but we first need a date column (or a date table).
Fiscal Year =
CONCATENATE("FY",
IF(MONTH('Table'[Date]) <=3,
VALUE(FORMAT('Table'[Date],"YY")),
VALUE(FORMAT('Table'[Date],"YY")) +1
)
)
You may check the thread below for reference:
Add the date column in the Axis of the visual, Fiscal Year as filter(We could also create Slicer visual here), then the date should be start from April. See:
For "revenue from the first and last month and display the growth in percentage", could you please explain a bit here?
What would you like to compare here, only with the last and first month values?
If yes, then we could take use of lookupvalue function to get the actual revenue data, then divide them.
I will share the testing results later, by the way, is the image you posted show your data sample? I will use that for testing.
If it is not your data sample, please share it. So that we will suggest a more suitable suggestion.
Regards
Hi v-micsh-msft
Thank you for your solution. I shall try that.
Here is a sample data
I want to compare first and last month values only. But I also want to be able to use a slicer when I want to compare a particular customer only.
-Shruti Mehta
Hi shrmehta,
Would it be available for you to share the data in a Table format?
I only need ten rows of the data sample which could represent your data structure. I am not able to figure out the data in your uploaded image, as the items in the image are clouding.
Regards
Hi v-micsh-msft
Including the entire table
Month FY Quarter Customer Group Name USD Apr-14 FY 15 Q 1 A 24,959.06 Apr-14 FY 15 Q 1 B 12,208.07 Apr-14 FY 15 Q 1 C 617,797.88 Apr-14 FY 15 Q 1 D 19,722.69 Apr-14 FY 15 Q 1 E 9,438.53 Apr-14 FY 15 Q 1 F 17,394.75 Apr-14 FY 15 Q 1 G 746,503.10 May-14 FY 15 Q 1 A 666,325.72 May-14 FY 15 Q 1 B 633,191.13 May-14 FY 15 Q 1 C 68,692.46 May-14 FY 15 Q 1 D 24,959.06 May-14 FY 15 Q 1 E 16,249.50 May-14 FY 15 Q 1 F 13,841.54 May-14 FY 15 Q 1 G 12,614.67 Jun-14 FY 15 Q 1 A 683,747.14 Jun-14 FY 15 Q 1 B 670,715.35 Jun-14 FY 15 Q 1 C 68,420.27 Jun-14 FY 15 Q 1 D 31,334.72 Jun-14 FY 15 Q 1 E 24,959.06 Jun-14 FY 15 Q 1 F 14,883.75 Jun-14 FY 15 Q 1 G 13,814.15 Jul-14 FY 15 Q 2 A 683,757.66 Jul-14 FY 15 Q 2 B 588,355.13 Jul-14 FY 15 Q 2 C 71,060.72 Jul-14 FY 15 Q 2 D 67,943.27 Jul-14 FY 15 Q 2 E 39,225.95 Jul-14 FY 15 Q 2 F 30,183.77 Jul-14 FY 15 Q 2 G 13,495.87 Aug-14 FY 15 Q 2 A 683,747.03 Aug-14 FY 15 Q 2 B 640,403.00 Aug-14 FY 15 Q 2 C 67,032.10 Aug-14 FY 15 Q 2 D 62,497.81 Aug-14 FY 15 Q 2 E 42,950.00 Aug-14 FY 15 Q 2 F 17,089.78 Aug-14 FY 15 Q 2 G 13,367.00 Sep-14 FY 15 Q 2 A 689,211.15 Sep-14 FY 15 Q 2 B 651,497.33 Sep-14 FY 15 Q 2 C 103,322.11 Sep-14 FY 15 Q 2 D 67,128.44 Sep-14 FY 15 Q 2 E 42,650.00 Sep-14 FY 15 Q 2 F 19,221.75 Sep-14 FY 15 Q 2 G 13,334.02 Oct-14 FY 15 Q 3 A 685,930.36 Oct-14 FY 15 Q 3 B 648,580.70 Oct-14 FY 15 Q 3 C 398,016.14 Oct-14 FY 15 Q 3 D 66,453.37 Oct-14 FY 15 Q 3 E 63,845.75 Oct-14 FY 15 Q 3 F 42,650.00 Oct-14 FY 15 Q 3 G 15,355.23 Nov-14 FY 15 Q 3 A 685,882.18 Nov-14 FY 15 Q 3 B 630,634.31 Nov-14 FY 15 Q 3 C 579,056.88 Nov-14 FY 15 Q 3 D 99,046.94 Nov-14 FY 15 Q 3 E 66,195.41 Nov-14 FY 15 Q 3 F 42,650.00 Nov-14 FY 15 Q 3 G 18,112.00 Dec-14 FY 15 Q 3 A 685,859.90 Dec-14 FY 15 Q 3 B 628,403.73 Dec-14 FY 15 Q 3 C 555,977.91 Dec-14 FY 15 Q 3 D 88,698.08 Dec-14 FY 15 Q 3 E 72,903.52 Dec-14 FY 15 Q 3 F 39,400.28 Dec-14 FY 15 Q 3 G 14,437.80 Jan-15 FY 15 Q 4 A 682,857.90 Jan-15 FY 15 Q 4 B 583,480.04 Jan-15 FY 15 Q 4 C 347,321.12 Jan-15 FY 15 Q 4 D 92,871.79 Jan-15 FY 15 Q 4 E 72,369.75 Jan-15 FY 15 Q 4 F 40,461.10 Jan-15 FY 15 Q 4 G 17,781.50 Feb-15 FY 15 Q 4 A 673,210.35 Feb-15 FY 15 Q 4 B 649,678.36 Feb-15 FY 15 Q 4 C 415,308.09 Feb-15 FY 15 Q 4 D 93,216.69 Feb-15 FY 15 Q 4 E 75,885.39 Feb-15 FY 15 Q 4 F 40,300.00 Feb-15 FY 15 Q 4 G 22,256.76 Mar-15 FY 15 Q 4 A 739,030.14 Mar-15 FY 15 Q 4 B 672,688.31 Mar-15 FY 15 Q 4 C 648,282.83 Mar-15 FY 15 Q 4 D 125,675.88 Mar-15 FY 15 Q 4 E 92,664.20 Mar-15 FY 15 Q 4 F 40,300.00 Mar-15 FY 15 Q 4 G 21,270.40 Apr-15 FY 16 Q 1 A 910,786.40 Apr-15 FY 16 Q 1 B 838,476.55 Apr-15 FY 16 Q 1 C 672,602.40 Apr-15 FY 16 Q 1 D 609,004.88 Apr-15 FY 16 Q 1 E 109,387.64 Apr-15 FY 16 Q 1 F 92,345.22 Apr-15 FY 16 Q 1 G 58,055.48 May-15 FY 16 Q 1 A 917,949.20 May-15 FY 16 Q 1 B 798,071.82 May-15 FY 16 Q 1 C 672,573.83 May-15 FY 16 Q 1 D 563,909.79 May-15 FY 16 Q 1 E 108,874.61 May-15 FY 16 Q 1 F 99,204.79 May-15 FY 16 Q 1 G 97,121.48 Jun-15 FY 16 Q 1 A 8,867,272.78 Jun-15 FY 16 Q 1 B 864,595.38 Jun-15 FY 16 Q 1 C 672,669.77 Jun-15 FY 16 Q 1 D 587,836.57 Jun-15 FY 16 Q 1 E 128,142.07 Jun-15 FY 16 Q 1 F 107,800.27 Jun-15 FY 16 Q 1 G 82,756.97 Jul-15 FY 16 Q 2 A 145,182.05 Jul-15 FY 16 Q 2 B 127,001.07 Jul-15 FY 16 Q 2 C 40,300.00 Jul-15 FY 16 Q 2 D 21,339.14 Jul-15 FY 16 Q 2 E 15,403.12 Jul-15 FY 16 Q 2 F 6,526.25 Jul-15 FY 16 Q 2 G 465.41 Aug-15 FY 16 Q 2 A 1,101,051.52 Aug-15 FY 16 Q 2 B 834,214.52 Aug-15 FY 16 Q 2 C 663,638.65 Aug-15 FY 16 Q 2 D 590,827.99 Aug-15 FY 16 Q 2 E 197,747.95 Aug-15 FY 16 Q 2 F 155,752.04 Aug-15 FY 16 Q 2 G 147,927.23 Sep-15 FY 16 Q 2 A 1,046,057.75 Sep-15 FY 16 Q 2 B 927,190.60 Sep-15 FY 16 Q 2 C 709,472.67 Sep-15 FY 16 Q 2 D 563,062.79 Sep-15 FY 16 Q 2 E 236,218.29 Sep-15 FY 16 Q 2 F 169,333.56 Sep-15 FY 16 Q 2 G 144,294.97 Oct-15 FY 16 Q 3 A 2,780,386.82 Oct-15 FY 16 Q 3 B 1,049,473.82 Oct-15 FY 16 Q 3 C 858,644.08 Oct-15 FY 16 Q 3 D 563,968.74 Oct-15 FY 16 Q 3 E 426,858.55 Oct-15 FY 16 Q 3 F 155,073.34 Oct-15 FY 16 Q 3 G 153,563.13 Nov-15 FY 16 Q 3 A 132,042.42 Nov-15 FY 16 Q 3 B 130,633.85 Nov-15 FY 16 Q 3 C 60,818.34 Nov-15 FY 16 Q 3 D 46,468.69 Nov-15 FY 16 Q 3 E 19,798.47 Nov-15 FY 16 Q 3 F 456.40 Nov-15 FY 16 Q 3 G (28,686.67) Dec-15 FY 16 Q 3 A 1,114,863.98 Dec-15 FY 16 Q 3 B 1,010,288.07 Dec-15 FY 16 Q 3 C 888,850.79 Dec-15 FY 16 Q 3 D 601,190.44 Dec-15 FY 16 Q 3 E 550,225.79 Dec-15 FY 16 Q 3 F 226,424.60 Dec-15 FY 16 Q 3 G 140,539.45 Jan-16 FY 16 Q 4 A 1,221,077.53 Jan-16 FY 16 Q 4 B 1,135,349.83 Jan-16 FY 16 Q 4 C 863,530.80 Jan-16 FY 16 Q 4 D 588,382.43 Jan-16 FY 16 Q 4 E 534,577.49 Jan-16 FY 16 Q 4 F 249,490.21 Jan-16 FY 16 Q 4 G 144,523.53 Feb-16 FY 16 Q 4 A 1,162,356.30 Feb-16 FY 16 Q 4 B 862,032.55 Feb-16 FY 16 Q 4 C 634,191.59 Feb-16 FY 16 Q 4 D 580,227.57 Feb-16 FY 16 Q 4 E 534,793.82 Feb-16 FY 16 Q 4 F 266,279.21 Feb-16 FY 16 Q 4 G 152,122.93 Mar-16 FY 16 Q 4 A 1,418,886.31 Mar-16 FY 16 Q 4 B 1,253,947.77 Mar-16 FY 16 Q 4 C 728,817.69 Mar-16 FY 16 Q 4 D 589,371.75 Mar-16 FY 16 Q 4 E 553,435.24 Mar-16 FY 16 Q 4 F 364,436.39 Mar-16 FY 16 Q 4 G 290,963.40 Apr-16 FY 17 Q 1 A 1,539,402.43 Apr-16 FY 17 Q 1 B 1,250,625.85 Apr-16 FY 17 Q 1 C 595,116.01 Apr-16 FY 17 Q 1 D 580,387.20 Apr-16 FY 17 Q 1 E 528,058.41 Apr-16 FY 17 Q 1 F 308,263.33 Apr-16 FY 17 Q 1 G 150,746.44 May-16 FY 17 Q 1 A 1,531,447.85 May-16 FY 17 Q 1 B 1,285,027.76 May-16 FY 17 Q 1 C 654,737.79 May-16 FY 17 Q 1 D 591,821.10 May-16 FY 17 Q 1 E 499,918.72 May-16 FY 17 Q 1 F 254,173.61 May-16 FY 17 Q 1 G 146,508.60 Jun-16 FY 17 Q 1 A 1,581,348.88 Jun-16 FY 17 Q 1 B 1,507,006.18 Jun-16 FY 17 Q 1 C 588,081.83 Jun-16 FY 17 Q 1 D 578,200.40 Jun-16 FY 17 Q 1 E 561,126.57 Jun-16 FY 17 Q 1 F 334,345.69 Jun-16 FY 17 Q 1 G 129,429.41 Regards