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

 

  • daniel1983's avatar
    daniel1983
    Frequent Visitor
    4 years ago

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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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