Forum Discussion

daniel1983's avatar
daniel1983
Frequent Visitor
4 years ago
Solved

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 (...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    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's avatar
    ryan_mayu
    4 years ago

    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))

  • ryan_mayu's avatar
    ryan_mayu
    4 years ago
    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's avatar
daniel1983
Frequent Visitor
4 years ago

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.

CompanyFiscal YearSales
AFY214
BFY216
CFY2110
AFY203
BFY206
CFY205

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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.