Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating Price Elasticity

Good day,

 

First the data:

 

ClientNumberDateIce CreamsRate
12342019/12/012000010
12342019/12/01100010
12342019/12/01500011
12342019/12/02200012
12342019/12/02900011
12342019/12/02500013
12342019/12/03600014
12342019/12/04600012
12342019/12/05500011
12582019/12/05200020
12582019/12/05100010
12582019/12/05600015
12582019/12/05500012
12582019/12/06100013
12582019/12/06400014
12582019/12/06500015
12582019/12/06600020
12582019/12/06400010
12582019/12/0650011

 

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,

1 ACCEPTED SOLUTION
kentyler
Solution Sage
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.icecream_add_index.png
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
previousIceCreamAmount.png
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

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

3 REPLIES 3
kentyler
Solution Sage
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.icecream_add_index.png
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
previousIceCreamAmount.png
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

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

That would work well, but there is just a slight error in the method - as when the client no changes, it has to start over again. But the solution would work perfectly if we can make this happen.

Anonymous
Not applicable

Managed to get the index per client working with the below:

 

SequenceColumn1 =
VAR CurrentValue = Sheet1[Index]
RETURN COUNTROWS (
FILTER (
CALCULATETABLE (
Sheet1;
ALLEXCEPT ( Sheet1; Sheet2[ClientNo] )
);
Sheet1[Index] <= CurrentValue
)
)

 

Thanks for the help! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors