Forum Discussion
Help - % Change Between Fiscal Years
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 |
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
pls see the attachment below
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))- Measure =VAR _max=max('Table'[Fiscal year2])VAR _last=_max-1return DIVIDE(CALCULATE(sum('Table'[Sales]),'Table'[Fiscal year2]=_max),CALCULATE(sum('Table'[Sales]),'Table'[Fiscal year2]=_last))-1
15 Replies
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/aU2aKbnHuWsAlso, 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_0uAyou 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
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!
pls see the attachment below
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).
