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

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

       

      • 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.