Dynamic Flag on dates based on a date slicer

[Anonymous]

Hello, 

I'm stuck cause of a condition I fail to apply in DAX. 

In my FactTable, I have customers (NAME), amount and date of purchase.

CUSTOMER        DATE

alexis                  22/09/2017

alexis                  24/05/2016

charle                22/10/2017                  

bea                   04/06/2016

I have a CalendarTable which is related to the FactTable DATE <=> DATE OF PURCHASE

On my screen, I'll have a relative date slicer (with my 'Calendar'[Date]).
If my MIN(DATE) is, for example, 19/10/2017, I would like to count all the customers who have purchased something in 2017 and in 2016.

The management rule is :

Count the number of customers who have make at least one purchase the selected year (N) and at least one purchase the previous year (N-1). 

My problem is I do not achieve to flag this ! I can't use a calculated column because it is based on a dynamic filter but how can I filter it ? 

I tried :

VAR SelectedYear = YEAR( MIN (Calendar[Date]))

VAR PreviousSelectedYear = YEAR( MIN (Calendar[Date]))-1

RETURN

CALCULATE (

DISTINCTCOUNT (FactTable[Customer] ; 

FILTER ( YEAR (MAX (FactTable[Date of purchase] = SelectedYear ) && 

YEAR (MAX (FactTable[Date of purchase] = PreviousSelectedYear )))

But it doesn't work because I'm using the MAX function. 


How can I "scan" all of my date based on a dynamic slicer ? 

Thanks by advance for ur help,

[Fowmy · Accepted solution]

@Anonymous 

I have created a measure that you can use to filter customers who purchased at least once in current asn last year. you can assing it to visual filter or modify with amount.

You can download the file: HERE

Measure = 
VAR CY =  YEAR(MIN('Calendar'[Date]))
VAR LY =  YEAR(MIN('Calendar'[Date]))-1

VAR CYCUST = 
    CALCULATETABLE(
        VALUES('Fact Table'[Customer]),
        'Calendar'[Year #] = CY,
        ALL('Calendar')
    )
VAR LYCUST = 
    CALCULATETABLE(
        VALUES('Fact Table'[Customer]),
        'Calendar'[Year #] = LY,
        ALL('Calendar')
    )
RETURN

IF(
    COUNTROWS(INTERSECT( CYCUST , LYCUST )) > 0,
    1,
    0
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[lbendlin]

You don't want to use your dates table for the slicer. Instead, create a calculated table that has just the date column from your dates table (via VALUES()) and feed the slicer from there.   Then you can create a measure that pulls the SELECTEDVALUE() from the slicer and does the required date math.

[Anonymous]

Thanks for answering but I don't understand your point. My problem is that I want to check multiple values on a single column, group by customers. 

I tried your solution but this doesnt change anything to my measure's problem..

Can you maybe explain to me more deeply tour thought ? 

Thx by advance,