Help - % Change Between Fiscal Years

[daniel1983]

Hi - I'm struggling with this and hope someon can assist!

A sample of my data is below. For a number of companies I have sales data for each fiscal year. The fiscal years are not calendar years (there is a March year end). But the fiscal years are (many to many) linked to a calendar table. I am looking for a measure to calculate the percentage increase in sales between fiscal years for each company. For example, moving from FY19 to FY20 the calculation would be:

Company A: (3-2)/2 = 50%

Company B: (6-3)/3 = 100% 

I've found many posts where this calculation is done for calendar years (e.g., 2019 vs. 2020) but am tripped up by having defined fiscal years.  Any advice would be greatly appreciated.

Company	Fiscal Year	Sales
A	FY21	4
B	FY21	6
A	FY20	3
B	FY20	6
A	FY19	2
B	FY19	3

[ryan_mayu · Accepted solution]

@daniel1983 

you can create a column

Column = 
VAR _last=maxx(FILTER('Table','Table'[Company]=EARLIER('Table'[Company])&&'Table'[FY]=EARLIER('Table'[FY])-1),'Table'[Sales])
RETURN if(ISBLANK(_last),BLANK(),'Table'[Sales]/_last-1)

or create a measure

Measure = 
VAR _last=SUMX(FILTER(all('Table'),'Table'[Company]=max('Table'[Company])&&'Table'[FY]=max('Table'[FY])-1),'Table'[Sales])
return if(ISBLANK(_last),BLANK(), sum('Table'[Sales])/_last-1)

pls see the attachment below

[ryan_mayu · Accepted solution]

@daniel1983 

pls see the attachment below

[ryan_mayu · Accepted solution]

pls try this

Measure = 
VAR _last=SUMX(FILTER(all('Table'),'Table'[Company]=max('Table'[Company])&&'Table'[FY]=MAX('Table'[FY])-1),'Table'[Sales])
VAR _last2=SUMX(FILTER(all('Table'),'Table'[FY]=MAX('Table'[FY])-1),'Table'[Sales])
return if(HASONEVALUE('Table'[Company]), if(ISBLANK(_last),BLANK(), sum('Table'[Sales])/_last-1),if(ISBLANK(_last2),BLANK(), sum('Table'[Sales])/_last2-1))

[Ashish_Mathur]

Hi,

It is actually quite a simple one to solve.  You may have a Date column in the base data or a Year and Month column (from which a Date column can then be created).

[ryan_mayu · Accepted solution]

@daniel1983 

you can create a column

Column = 
VAR _last=maxx(FILTER('Table','Table'[Company]=EARLIER('Table'[Company])&&'Table'[FY]=EARLIER('Table'[FY])-1),'Table'[Sales])
RETURN if(ISBLANK(_last),BLANK(),'Table'[Sales]/_last-1)

or create a measure

Measure = 
VAR _last=SUMX(FILTER(all('Table'),'Table'[Company]=max('Table'[Company])&&'Table'[FY]=max('Table'[FY])-1),'Table'[Sales])
return if(ISBLANK(_last),BLANK(), sum('Table'[Sales])/_last-1)

pls see the attachment below

[daniel1983]

This is amazing, thank you!

Here's my follow up question that I'm not trying to figure out: this gives me the % change by company, but is there a variation that will also give me the total % change?   For example, on my report I'd like to add a slicer that lets me pick company 'A', company 'B', or the total for all companies.  

The total would then show an aggregate of total sales for each fiscal year and how that changed year over year. 

Thanks again!

[ryan_mayu · Accepted solution]

@daniel1983 

pls see the attachment below

[daniel1983]

Thank you! Sorry but my system won't let me download the attachment. Any way you can copy/paste the text of the revised measure? Thx!

[ryan_mayu · Accepted solution]

pls try this

Measure = 
VAR _last=SUMX(FILTER(all('Table'),'Table'[Company]=max('Table'[Company])&&'Table'[FY]=MAX('Table'[FY])-1),'Table'[Sales])
VAR _last2=SUMX(FILTER(all('Table'),'Table'[FY]=MAX('Table'[FY])-1),'Table'[Sales])
return if(HASONEVALUE('Table'[Company]), if(ISBLANK(_last),BLANK(), sum('Table'[Sales])/_last-1),if(ISBLANK(_last2),BLANK(), sum('Table'[Sales])/_last2-1))

[daniel1983]

Hi @ryan_mayu 

Any thoughts on my question below (i.e., calculating the % change for a subset of companies - for example, if there are companies, A, B, and C, and we want to only calculate the % change for A and B)? I've been working on this for a while now and still can't figure it out. Any suggestions would be greatly apprceciated 🙂

[ryan_mayu]

@daniel1983 

could you pls provide the sample data and expected output? just like what you did in the first post.

[daniel1983]

For sure. Sample data is below.

What I'm looking for is if I select:

• "A" in my report, calculation shows me: (4-3)/3 = 33%
• "A and B" in my report, calculation shows me: ((4+6)-(3+6))/(3+6) = 11%
• "all companies" in my report, calculation shows me: ((4+6+10)-(3+6+5))/(3+6+5) = 43%

With the current measure, "A" and "all companies" (bullets 1 and 3) work. But I can't get the year over year percentage change for the second bullet, where I'm just pulling a subset of companies.

Company	Fiscal Year	Sales
A	FY21	4
B	FY21	6
C	FY21	10
A	FY20	3
B	FY20	6
C	FY20	5

[ryan_mayu]

@daniel1983 

pls see the attachment below

[ryan_mayu · Accepted solution]

Measure =

VAR _max=max('Table'[Fiscal year2])

VAR _last=_max-1

return DIVIDE(CALCULATE(sum('Table'[Sales]),'Table'[Fiscal year2]=_max),CALCULATE(sum('Table'[Sales]),'Table'[Fiscal year2]=_last))-1

[daniel1983]

thanks so much - sorry, but do you mind copying/pasting the measure as text? my work computer won't let me download any attachments.

[Ashish_Mathur]

Hi,

From the Fiscal Year column, create a proper Date column (may be 1/1/2021).  Thereafter create a Calendar Table with a calculated column for the Fiscal Year.  Create a relationhip (Many to One and Single) from the Date column in your Data Table to the Date column in the Calendar Table.  to your visual, drag the Date column from the Calendar Table.  Create a master table of all Company names and a relationship (Many to One and Single) between the Company column of your Data Table to the Company name column of the master table.  Create a Company Name slicer from the new master table and select A and B.  Write these measures:

Revenue = sum(Data[Sales])

Revenue in previous year = calculate([Revenue],previousyear('Calendar'[Date]))

Growth in revenue over previous year (%) = divide(([revenue]-[Revenue in previous year]),[Revenue in previous year])

Hope this helps.

[daniel1983]

Thanks ryan_mayu - this works! Sorry to keep coming back to you, but this is slightly beyond my abilities 🙂   One more variation I am trying. Say there is a company 'C', and I'd like to select a subset of companies (e.g., A and C) and calculate the percentage increase in sales for those companies only. Is it possible to define a third varaible (_last3) and then update the return if statement to account for situations where you are only pulling a subset of companies?

[amitchandak]

@daniel1983 , You need to a separate FY or Date table 

You can Use FY or number  when it sortable or FY Rank

new column

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

or

Year Rank = RANKX(all('Date'),'Date'[FY],,ASC,Dense)

This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),h'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

check

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Also, check

How to use two Date/Period slicers https://youtu.be/WSeZr_-MiTg

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA