DAX - VAR_Count_Filters Assistance

[Mischa]

Hello!

Can I please request some masterminds to assist in the following problem?

I have recreated a table to illustrate the problem. 

Purchased date	Apple Type	Price	ANSWER
2021/07/08	Green Golden Apples	cheap
2021/07/09	Red Golden Apples	cheap
2021/07/10	Yellow Apples	expensive
2021/07/10	Purple Apples	expensive
2021/07/11	Blue Apples	expensive
2021/07/10	Blue Apples	expensive
2020/07/16	Green Golden Apples	cheap
2019/07/11	Yellow Apples	expensive
2020/07/17	Red Golden Apples	cheap
2020/07/18	Blue Golden Apples	expensive
2019/05/08	Green Apples	cheap
2019/05/09	Red Golden Apples	expensive
2018/08/01	Yellow Apples	expensive
2019/05/10	Purple Apples	cheap

What I want to calculate:

IF the price is expensive, calculate the number of times the same Apple Type was purchased in a 24 month sliding period before and including its purchase date, BUT exclude all ‘Golden’ types. If not, leave BLANK

I have tried the following logic:

ANSWER =

    VAR __CurrentID = APPLE[Apple Type]

    RETURN

    COUNTROWS(

        FILTER('APPLE',

                __CurrentID = APPLE[Apple Type]

            && APPLE[PRICE] = “expensive"

        )

)

Unfortunately it still does not take the ‘expensive’ filter into account.

I also don’t know where to start with the exclusion of ‘Golden’

Please help!

[amitchandak · Accepted solution]

@Mischa , Try new column like

ANSWER =
VAR _date = eomonth([Purchased date],-24)

if(APPLE[PRICE] = "expensive", countx(filter(APPLE, APPLE[Type] =earlier(APPLE[Type]) && search("Golden",APPLE[Type],,0) =0 && [Purchased date] <= earlier([Purchased date]) && [Purchased date] >=_date),[Purchased date])

[Mischa]

Thank you kindly sir!