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

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

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