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

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

  • daniel1983's avatar
    daniel1983
    Frequent Visitor
    4 years ago

    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?

  • daniel1983's avatar
    daniel1983
    Frequent Visitor
    4 years ago

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

      daniel1983 

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

      • 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