Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
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
Help when you know. Ask when you don't!
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
Help when you know. Ask when you don't!
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.
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!
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |