Calculating growth & Rearranging data lables

[shrmehta]

I have monthly data from April 14 to June 16 in this format (assuming only 2 customers)

I want to compare the revenue from the first and last month and display the growth in percentage.  Does someone know how I can do this?

Also while using bar chart / line chart I want the month to be displayed from April to March but Power BI automatically displays it from January to December. Is there a way to rearrange this?

[LuisQuedas]

Hi,

few questions to suggest an approach to answer your post,

- We need to create a  new column with the Month and Year, How is difined your Fiscal Year? April to March?

- When you say you want to compare First Month with Last Month, Does it mean Compare April 14 with Junw 16?

- You want to compare the all grosth, i.e., you don't want to split it by customer, isn't it?

Cheers, Luis

[shrmehta]

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

[v-micsh-msft]

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: 

Fiscal Year

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

[shrmehta]

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

[v-micsh-msft]

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

[shrmehta]

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