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)
MSC's avatar
MSC
Icon for Helper III rankHelper III
5 years ago

camargos88Ashish_Mathur  I think I've found the source of the problem: In the pbix I'm working with, the date column doesn't have the calendar symbol in front of it... even though the settings seem to be correct. The date type is date and format is dd.mm.yyy

 

How can I change this?

 

 

 

 

Hence, I think this causes the function and especially the term ".. && [Date]..." not to work as intended.

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

    I just realized that the problem might be that there's relationship between the table with the data and the date table. camargos88 How can I integrate the date table in your DAX function?