Dynamic measure to control customers who did not buy anything for last x days

[Szwadron]

Hi everyone,

I would like to ask you to help me with implementing a solution to show customers who DID not buy anything from the sales table for last x days (x is a parameter based on slicer described as NumberDay).

The scenario:

1) I have a table - SalesTable with thre collumns: Customer, Purchase Y/N, DateOfSales

Customer and DateOfSales are just values from a table, and Purchase Y/N is a measure with DAX code:

NoPurchase = 
var currentDate = LASTDATE('Calendar'[Date])
var SelectedPeriod = DATESBETWEEN('Calendar'[Date],DATEADD(currentDate,-NumberDay[Value NumberDays],DAY),currentDate)
var DatesInTheSelectedPeriod = CALCULATE(COUNTROWS('SalesTable'), 'Calendar'[Date] IN SelectedPeriod )
RETURN IF(DatesInTheSelectedPeriod = BLANK(), "Y", "N")

and my dashboard looks like that:

 

so on the screen with my solution those orders for Customer A who were placed later than 217 days in the past are described as Y, and those which were placed sooner are described as N.

Hower if I add to my parameter number 195 which was the last orderfrom that customer in the past everyhing is described as Y which means that no order have been placed sooner than 195 days ago and no I would like to implement a solution:

to show me those customers who did not buy anything from today till (TODAY - NumberDay) and return them as a table.

Is that achievable? 

I would be very grateful for any tips how to achieve that.

[amitchandak · Accepted solution]

@Szwadron ,

Remove Dateof Sales from visual or add removefilter (Table[DateofSales]) in meausre

having your rolling like

CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-NumberDay[Value NumberDays],DAY))

or

CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],today() ,-NumberDay[Value NumberDays],DAY))

[Szwadron]

Thank you very much for the reply but I am afraid I don't get it.. 

Shall I add the code you wrote for RemoveFilter somewhere in my current measure called 'NoPurchase'

or shall I create a new measure and paste your code? If so shall I add any filter to it? 

@amitchandak