Create a measure that checks whether a certain condition existed the previous year or 2 or 3 years a

[FBA94]

Hello,

I want to create a measure in DAX that checks whether the value of a column [Product] in the facts table has existed for 1, 2 Or 3 years, only when the quantity is greater than zero, and returns "no" if the conditions are met, otherwise "yes".

In my cube I have a table of facts containing the columns [product], [qty], [id date] and others ...

I also have a Date table (relation with fact table with id date as 20230616)

For example :

Product	Year	Qty
prd1	2023	1
prd1	2022	2
prd2	2021	12
prd2	2019	45
prd2	2018	3
prd3	2017	1
prd4	2020	0

Expected result :

Product	Year	Qty	My Measure
prd1	2023	1	No
prd1	2022	2	Yes
prd2	2021	12	No
prd2	2019	45	No
prd2	2018	3	Yes
prd3	2017	1	Yes
prd4	2020	0	Yes

Here's my measure, but I don't know how to add a year filter (compare a year with year -1 or year -2 or year -3):

 IF(
    COUNTROWS(
        FILTER('Fact Table';
            [Qty] > 0 &&
           SELECTEDVALUE('Fact Table'[Product])  <> BLANK()
        )
    ) > 0;
    "No";
    "Yes"
)

Thank you for your help !

[devanshi]

IF(

    COUNTROWS(

        FILTER('Fact Table';

            [Qty] > 0 &&

           'Fact Table[Date] >=Earlier('Fact Table'[Date]    && 'Fact Table[Date] <=Earlier('Fact Table'[Date])

        )

    ) >=1;

    "No";

    "Yes"

)

[tamerj1]

Hi @FBA94 

would you please explain why Prod3 is "Yes"?

[FBA94]

Hi @tamerj1 

Because there is no prd3 in 2016, 2015 and 2014

[tamerj1]

@FBA94 

Please try

=
IF (
ISEMPTY (
FILTER (
CALCULATETABLE ( 'Fact Table'; ALL ( 'Date' ) );
'Fact Table'[Date] < MIN ( 'Date'[Date] )
)
);
"Yes";
"No"
)

[FBA94]

Hi @tamerj1 

Thank you, i add my measure [qty] like this 

=
IF (
ISEMPTY (
FILTER (
CALCULATETABLE ( 'Fact Table'; ALL ( 'Date' ) );
'Fact Table'[Date] < MIN ( 'Date'[Date] ) && [Qty] > 0
)
);
"Yes";
"No"
)

But it doesn't work, I get empty and negative quantities with "Yes" and "No" (I've added a filter on the company) 

In other words, my measure didn't filter the quantity and the company. 

Company	Product	Qty	My Measure
cpy1	prd1	1	No
cpy1	prd1	2	Yes
cpy1	prd2	12	No
cpy1	prd2	45	No
cpy1	prd2	3	Yes
cpy1	prd3	1	Yes
cpy1	prd4	0	Yes
cpy1	prd5		Yes
cpy1	prd6	-55	No

[tamerj1]

@FBA94 

Is this the result you got or the expected result?

[FBA94]

The result i got

[tamerj1]

@FBA94 

Great. Would you please add a column for the expected results for better understanding?

[FBA94]

Only the products of my company "cpy1" which have a quantity > 0, for information I have a filter on the year and company, Here is an example with year = 2022 and company = cpy1 :

Company	Product	Qty	My Measure
cpy1	prd1	1	No
cpy1	prd2	12	No
cpy1	prd3	1	Yes
cpy1	prd4	35	Yes

Year = 2022 any company = cpy1 :

prd1 have a qty > 0 so mymeasure should be "No" beacause the same product prd1 exist in 2021 or 2020 or 2019

prd3 have a qty > 0 so mymeasure should be "Yes" beacause the same product prd3 not exist in 2021 or 2020 or 2019

So, if I have a "prdN" whose quantity is empty or negative, it won't appear in my result.

Thank you

[tamerj1 · Accepted solution]

@FBA94 
Apologies, I have totally missed your reply. 

Please try

=
IF (
    [Qty] > 0
        && ISEMPTY (
            FILTER (
                CALCULATETABLE ( 'Fact Table'; ALL ( 'Date' ) );
                'Fact Table'[Date] < MIN ( 'Date'[Date] )
            )
        );
    "Yes";
    "No"
)

[FBA94]

No problem, sorry I may have misspoken what I wanted to do.

I think the problem is in the logic of the && :

The test IF (qty > 0) AND (date < date) then "Yes".
ELSE all other possibilities (qty <=0 AND date > date) then "No". (not what i want)
Maybe something like this :

= 
IF (
    [Qty] > 0
        && ISEMPTY (
            FILTER (
                CALCULATETABLE ( 'Fact Table'; ALL ( 'Date'));
                'Fact Table'[Date] <  MIN('Date'[Date])
            )
        );
    "Yes";
    "No" //i want my measure not calculated for zero or negative quantity
)

Finally, I modify my test as follows

SWITCH( 
    TRUE();
    [Qty] > 0
        && ISEMPTY (
            FILTER (
                CALCULATETABLE ( 'Fact table'; ALL ( 'Date'));
                'Fact table'[Date] <  MIN('Date'[Date])
            )
        );
    "Yes";
    [Qty] <= 0;
    "Nothing";    
    "No"

)

@tamerj1 Thanks to you, I was able to understand and adapt my results and analysis to my needs.