cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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. 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.

1 ACCEPTED SOLUTION
Community Champion

@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)``````

Proud to be a Super User!

16 REPLIES 16
Community Champion

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

Proud to be a Super User!

Helper III

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

Community Champion

@MSC ,

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

Proud to be a Super User!

Helper III

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

Helper III

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

Helper III

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?

Helper III

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

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.

Community Champion

@MSC ,

Try this measure:

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

Check the attached file.

Proud to be a Super User!

Helper III

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

Helper III

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

Community Champion

@MSC ,

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

Proud to be a Super User!

Helper III

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

Community Champion

@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)``````

Proud to be a Super User!

Helper III

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