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

This is amazing, thank you!

 

Here's my follow up question that I'm not trying to figure out: this gives me the % change by company, but is there a variation that will also give me the total % change?   For example, on my report I'd like to add a slicer that lets me pick company 'A', company 'B', or the total for all companies.  

 

The total would then show an aggregate of total sales for each fiscal year and how that changed year over year. 

 

Thanks again!

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