topic Re: Create a measure that checks whether a certain condition existed the previous year or 2 or 3 years a in DAX Commands and Tips

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

FBA94 — Fri, 16 Jun 2023 14:36:43 GMT

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 !

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

tamerj1 — Fri, 16 Jun 2023 14:49:18 GMT

Hi FBA94

would you please explain why Prod3 is "Yes"?

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

FBA94 — Fri, 16 Jun 2023 15:13:27 GMT

Hi tamerj1

Because there is no prd3 in 2016, 2015 and 2014

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

tamerj1 — Fri, 16 Jun 2023 16:07:32 GMT

FBA94

Please try

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

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

FBA94 — Mon, 19 Jun 2023 14:00:10 GMT

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

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

tamerj1 — Mon, 19 Jun 2023 14:02:22 GMT

FBA94

Is this the result you got or the expected result?

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

FBA94 — Mon, 19 Jun 2023 14:04:51 GMT

The result i got

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

tamerj1 — Mon, 19 Jun 2023 14:06:20 GMT

FBA94

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

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

FBA94 — Mon, 19 Jun 2023 14:28:21 GMT

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

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

tamerj1 — Tue, 20 Jun 2023 07:42:21 GMT

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" )

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

devanshi — Tue, 20 Jun 2023 08:55:14 GMT

IF(

COUNTROWS(

FILTER('Fact Table';

[Qty] > 0 &&

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

)

) >=1;

"No";

"Yes"

)

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

FBA94 — Tue, 20 Jun 2023 16:03:09 GMT

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.