How to refer to the results of the same measure

[hnsbhat]

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

[v-huizhn-msft]

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.

right result
Best Regards,
Angelia

[hnsbhat]

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!

[v-huizhn-msft · Accepted 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