Calculate sales growth using calculated column

[MSC]

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. For me, the difficulty is also in the condition that the function should pay attention to each product's first year in the table and it thus should return blank in these years.

In general, my data looks like this.

Product	Date	Sales
A	31.12.19	4.242
A	31.12.18	2.455
A	31.12.17	1.543
A	31.12.16	416
A	31.12.15	264
B	31.12.19	6.363
B	31.12.18	4.683
B	31.12.17	2.315
B	31.12.16	1.624
B	31.12.15	396
C	31.12.19	9.954
C	31.12.18	6.524
C	31.12.17	3.425
C	31.12.16	2.641
C	31.12.15	1.256

You can find the excel file here.

https://github.com/MSC791/Growth_Table.git 

Thank you very much in advance for your support and suggestions!

[camargos88]

@MSC ,

_Average = CALCULATE(AVERAGE('Table'[Growth]), ALL('Table'[Product]), ALL(TABLE[Date]))

So you will ignore the selected date and get all the date values to calculate the average.

[MSC]

@camargos88I've used this function:

Average Measure =

VAR CustomerGroup_ =

    DISTINCT(Data_Table[Customer])

RETURN

    CALCULATE(AVERAGE(Data_Table2[Growth]), Data_Table[Category] IN CustomerGroup_, ALL(Data_Table[Customer]) )

Thanks a lot for your inputs and your help!

[camargos88]

@MSC ,

Are you filtering the values by this table ? So that's why the average is wrong ?

[MSC]

@camargos88I'm filterting the values by the data table where I have the products, the categories, and the sales.

[MSC]

@camargos88@Ashish_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]

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?

[MSC]

@Ashish_MathurI've uploaded a little demo dashboard with the DAX functions provided by @camargos88 which shows the results I'd like to get. If you've a measure solution, you're welcome.
However, the functions don't work with my real dataset even though the underlying data has the same structure as the demo data. At the moment, I'm trying to figure out why this happens.

[Ashish_Mathur]

Why do you want a calculated column solution?  Why not a measure?

[MSC]

Hi @Ashish_Mathur !

Initially, I preferred a column solution because I'd found a workaround for a similar problem which was based on calculated columns.

However, if there's a measure solution which returns the average category growth rate of the product I selected, this would be fine as well.

[camargos88]

@MSC ,

Try this measure:

_Average = CALCULATE(AVERAGE('Table'[Growth]), ALL('Table'[Product]))

Check the attached file.

[MSC]

@camargos88  Thank you very much for your help so far! I really appreciate it!

Interestingly, the measure you suggested works perfect on my demo dashboard but there seems to be a little problem in my real dataset. There, the average measure returns the normal growth rates of the selected item but not the average growth rate of the iteam category.

I've to figured that out and will come back to you tomorrow.

[MSC]

@camargos88I've just uploaded on expanded version of the file.

[camargos88]

@MSC ,

Can you provide the input data and the desired output ? I can work on it.

[MSC]

@camargos88Yes, of course. Thanks. I'll notify you as soon as the prepared data is uploaded on github.

[camargos88 · Accepted solution]

@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]

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