Forum Discussion

MSC's avatar
MSC
Icon for Helper III rankHelper III
5 years ago
Solved

Calculate sales growth using calculated column

Hi everyone!   I'd like to create a calculated column which returns the growth rate of my product sales. I've already managed to do with by means of measures but failed with a calculated column. Fo...
Need Help
Show and Tell
  • camargos88's avatar
    camargos88
    5 years ago

    MSC ,

     

    Try this code for a new calculated column:

    Growth = 
VAR _lastDate = MAXX(FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] < EARLIER('Table'[Date])), 'Table'[Date])
VAR _lastValue = CALCULATE(SUM('Table'[Sales]), FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] = _lastDate))

RETURN IF(_lastValue <> BLANK(),  ('Table'[Sales] - _lastValue) / _lastValue)
camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
5 years ago

MSC ,

 

Try this code for a new calculated column:

Growth = 
VAR _lastDate = MAXX(FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] < EARLIER('Table'[Date])), 'Table'[Date])
VAR _lastValue = CALCULATE(SUM('Table'[Sales]), FILTER('Table', 'Table'[Product] = EARLIER('Table'[Product]) && [Date] = _lastDate))

RETURN IF(_lastValue <> BLANK(),  ('Table'[Sales] - _lastValue) / _lastValue)
MSC's avatar
MSC
Icon for Helper III rankHelper III
5 years ago

camargos88Cool! Thank you very much for your quick response! I'll mark it as the solution. However, I've a follow-up question you might be able to help me with. Imagine product A and B belong to product category Alpha and C belongs to Beta.
Now, I'd like a function to return the average growth rate of Alpha, when I filter for product A on my dashboard. Do you know how this can be done?