Forum Discussion
Calculating Price Elasticity
Good day,
First the data:
| ClientNumber | Date | Ice Creams | Rate |
| 1234 | 2019/12/01 | 20000 | 10 |
| 1234 | 2019/12/01 | 1000 | 10 |
| 1234 | 2019/12/01 | 5000 | 11 |
| 1234 | 2019/12/02 | 2000 | 12 |
| 1234 | 2019/12/02 | 9000 | 11 |
| 1234 | 2019/12/02 | 5000 | 13 |
| 1234 | 2019/12/03 | 6000 | 14 |
| 1234 | 2019/12/04 | 6000 | 12 |
| 1234 | 2019/12/05 | 5000 | 11 |
| 1258 | 2019/12/05 | 2000 | 20 |
| 1258 | 2019/12/05 | 1000 | 10 |
| 1258 | 2019/12/05 | 6000 | 15 |
| 1258 | 2019/12/05 | 5000 | 12 |
| 1258 | 2019/12/06 | 1000 | 13 |
| 1258 | 2019/12/06 | 4000 | 14 |
| 1258 | 2019/12/06 | 5000 | 15 |
| 1258 | 2019/12/06 | 6000 | 20 |
| 1258 | 2019/12/06 | 4000 | 10 |
| 1258 | 2019/12/06 | 500 | 11 |
I would like to calculate the price elasticity of a client. In the above scenario, the price of the ice creams fluctuate a few times during a day and the quantities purchased does to. In order for you to calculate the Price elasticity the below formula needs to be used:
(Amount Bought) - (Amount Bought in Previous Row) Divided by ((Amount Bought)+(Amount Bought in Previous Row)/2)
There same then needs to be done with the Rates:
(Rate) - (Rate in Previous Row) Divided by ((Rate)+(Rate in Previous Row)/2)
Is there any way to do this in DAX? So that the calculation will be done per client in the list, per time period selected on the front end?
Thanks in advance.
Regards,
By far the easiest way to do this is to add a Previous Ice Creams and Previous Amount column to your data before bringing it into DAX, if that is possible.
If you can't do that, then calculating "previous row" is complex in DAX
First you need to add an index column.
Once you have the index you can subtract 1 from the current index to get the previous index (you have to decide how to handle the first row, which has no previous) and then use the previous index to look up the values you need from the previous row.Here is a sample measure that looks up the previous ice cream amount
Previous Ice Cream =VAR current_index = MAX(Sheet1[Index])VAR previous_index = IF(current_index = 0,blank(),current_index -1)VAR previous_ice_creams = IF(current_index = 0,blank(), calculate(max(sheet1[Ice Creams]),ALL(Sheet1),Sheet1[Index] = previous_index))Return previous_ice_creams
You would write a similar measure for the previous rate and then refer to those measures in the 2 measures you need to calculate elasticity.
If you need help with the further measures let me know.I learn something every time I answer a question
3 Replies
- kentyler
Solution Sage
By far the easiest way to do this is to add a Previous Ice Creams and Previous Amount column to your data before bringing it into DAX, if that is possible.
If you can't do that, then calculating "previous row" is complex in DAX
First you need to add an index column.
Once you have the index you can subtract 1 from the current index to get the previous index (you have to decide how to handle the first row, which has no previous) and then use the previous index to look up the values you need from the previous row.Here is a sample measure that looks up the previous ice cream amount
Previous Ice Cream =VAR current_index = MAX(Sheet1[Index])VAR previous_index = IF(current_index = 0,blank(),current_index -1)VAR previous_ice_creams = IF(current_index = 0,blank(), calculate(max(sheet1[Ice Creams]),ALL(Sheet1),Sheet1[Index] = previous_index))Return previous_ice_creams
You would write a similar measure for the previous rate and then refer to those measures in the 2 measures you need to calculate elasticity.
If you need help with the further measures let me know.I learn something every time I answer a question