Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I want to create a measure which refers to the result of the same measure for the previous rows. I want a Criteria Measure in the below table for which criteria is : IF ((<low> less then low -2) AND <close> less than <close> of previous date which has criteria as 'Yes') THEN "Yes"
Below is the sample data and I have manually put the Criteria to illustrate what I want. Is there any way we can achieve this in power pivot? Thanks in advance for any help here.
<date> | <ticker> | <open> | <high> | <low> | <close> | <rank> | Low -2 Measure | Criteria Measure |
9/12/2017 | xyz | 184.95 | 193.30 | 184.95 | 192.35 | 7 | ||
9/13/2017 | xyz | 192.80 | 194.40 | 188.60 | 189.55 | 8 | ||
9/14/2017 | xyz | 190.50 | 198.00 | 190.10 | 196.05 | 9 | 184.95 | |
9/15/2017 | xyz | 195.95 | 196.65 | 189.05 | 190.10 | 10 | 188.60 | |
9/18/2017 | xyz | 191.75 | 192.80 | 189.10 | 189.75 | 11 | 190.10 | Yes |
9/19/2017 | xyz | 191.70 | 192.95 | 187.50 | 189.90 | 12 | 189.05 | |
9/20/2017 | xyz | 190.40 | 190.90 | 185.00 | 185.50 | 13 | 189.10 | Yes |
9/21/2017 | xyz | 185.00 | 185.75 | 180.60 | 182.95 | 14 | 187.50 | Yes |
9/22/2017 | xyz | 180.65 | 181.45 | 175.15 | 175.85 | 15 | 185.00 | Yes |
9/25/2017 | xyz | 175.50 | 175.70 | 164.50 | 167.50 | 16 | 180.60 | Yes |
9/26/2017 | xyz | 168.05 | 174.45 | 167.25 | 171.60 | 17 | 175.15 | |
9/27/2017 | xyz | 173.90 | 174.10 | 164.45 | 165.65 | 18 | 164.50 | Yes |
Solved! Go to Solution.
Hi @hnsbhat,
If you want to get "Yes" for rows where <close> less than <close> of previous date which has criteria as 'Yes'. You just need to create another calculated column using the formula and get expected result.
= IF ( [<low>] <= [Low -2 Measure] && [<close>] <= [<close> of previous date] && LOOKUPVALUE ( [Criteria Measure], [<rank>], [<rank>] - 1 ) = "Yes", "Yes", BLANK () )
Best Regards,
Angelia
Hi @hnsbhat,
Of course, you can achieve it in Power Pivot. Please follow the steps below.
1. Add your sample table into pivot model.
2. Create a calculated column to get <close> less than <close> using the formula.
=LOOKUPVALUE([<close>],[<rank>],[<rank>]-1)
3. Create another calculated column to get Criteria Measure using the formula.
=IF([<low>]<=[Low -2 Measure]&&[<close>]<=[<close> of previous date],"Yes",BLANK())
Please review the expected result as the screenshot shown.
Best Regards,
Angelia
Hi v-huizhn-msft, Thanks for your reply, however it looks like the solution you have provided checks only if the close is less than previous close. But what I need is - <close> less than <close> of previous date which has criteria as 'Yes'. That means even if a close is less than previous close but more than the previous date which has Criteria "Yes", then I should not get "Yes" for current date. Only when the close is less than the close of previous date with criteria 'Yes' I need to get 'Yes' in current row.
One more thing to consider is in future I can add few more conditions to the criteria, but the last condition should be to check if the current close is less then close of previous date which fullfilled the criteria. Hope this is clear. Thanks!
Hi @hnsbhat,
If you want to get "Yes" for rows where <close> less than <close> of previous date which has criteria as 'Yes'. You just need to create another calculated column using the formula and get expected result.
= IF ( [<low>] <= [Low -2 Measure] && [<close>] <= [<close> of previous date] && LOOKUPVALUE ( [Criteria Measure], [<rank>], [<rank>] - 1 ) = "Yes", "Yes", BLANK () )
Best Regards,
Angelia
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |