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

    yoy.pbix20 KB
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    daniel1983 

    pls see the attachment below

    yoy-v2.pbix20 KB
  • 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

daniel1983 

pls see the attachment below

yoy-v2.pbix20 KB
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.